MySQL Date Time 252 codes

  1. Home
  2. MySQL
  3. Date Time
1 %a is the abbreviated weekday name, %D is the day of month with the suffix attached, %b is the abbreviated mon
2 %m returns the month (01-12), %d returns the day (01-31), and %Y returns the year in four digits
3 Add 7 day interval to a date
4 Add a YEAR column type
5 Add an incorrect date to a special table and show the result
6 Add date with interval
7 Add hour, seconds and microseconds to Date
8 Add Microsecond to time
9 Add Month and day to a date
10 Add one day to the date literal 2004-13-12; next show the error messages
11 Add time with ADDTIME function
12 Add times with SUM
13 ADDDATE (2005-12-31, INTERVAL 2 month)
14 ADDDATE (date, INTERVAL n i) adds n times the interval i to the starting date date
15 ADDDATE(2005-12-31, INTERVAL 3
16 Adding 3 days and 4 hours produces this result
17 Adding a Temporal Interval to a Date
18 Adding a Temporal Interval to a Time
19 Alias column for constant time value
20 Another way to compute a years length is to compute the date of the last day of the year and pass it to DAYOF
21 Birthdays in the upcoming month
22 Breaking Down Time Intervals into Components
23 Calculate a numerical value for the day, as it falls in the year
24 Calculate an age as of the beginning 1975 for someone born on 1965-03-01
25 Calculate the difference in time between NOWand UTC_TIMESTAMP
26 Calculate the last day of the month for the previous, current, and following months relative to a given date
27 Calculate the working time
28 Calculated dates are useful for range testing
29 Calculates the sum of the two TIME values
30 Calculating Intervals Between Dates
31 Calculating Intervals Between Times
32 Calculating One Date from Another by Substring Replacement
33 Calculation on Date data type
34 Change time zone
35 Change time zone for now
36 Check current date in where clause
37 Check date difference
38 Check the time zone
39 Check year value
40 Common used flags for DATE_FORMAT()
41 Compare date 2
42 Compare date in where clause
43 Compare the year value in where clause
44 Compare time value
45 Compare time values by using the TIMEDIFF() function
46 Comparing Times to One Another
47 Convert Date to int
48 Converting Between Date-and-Time Values and Seconds
49 CURDATE(), CURRENT_DATE(), CURTIME(), CURRENT_TIME(), CURRENT_TIMESTAMP(), and NOW() Functions
50 Date and Time Column Types
51 Date and Time Sizes, Formats, and Ranges
52 Date Calculations
53 Date data type manipulation
54 Date function
55 Date Symbols in DATE_FORMAT, TIME_FORMAT, and FROM_UNIXTIME
56 DATE_ADD() add a time interval to a given date
57 DATE_FORMAT function
58 DATE_FORMAT Specifiers
59 Date-Based Sorting
60 Date-Based Summaries
61 DayName returns Monday, Tuesday
62 DAYNAME( ) is useful in conjunction with other date-related techniques
63 DAYNAME( ) returns the complete day name
64 DAYOFWEEK( ) returns values from 1 to 7, corresponding to Sunday through Saturday
65 DAYOFYEAR
66 DAYOFYEAR( ) function is useful for calendar day sorting
67 Decomposing Dates and Times Using Formatting Functions
68 Decomposing Dates or Times Using Component-Extraction Functions
69 Decomposing Dates or Times Using String Functions substrings
70 Define and check the date data type
71 Define and use Date data type
72 Defining Set Membership
73 Determine the differences between dates and times
74 Determining Ages in Months
75 Determining the Current Date or Time
76 Determining the Current Time
77 Determining the Number of Records by Day, Month, and Year
78 Display DATETIME values from the datetime_val table using formats that include the time of day
79 Displaying TIMESTAMP Values in Readable Form
80 Distinct year value
81 Extract just the date
82 Extract only the month by using the MONTH() function
83 Extract the day of the month out of a value
84 Extract the entire time value by using the TIME() function
85 Extract the hour from the time value
86 Extract the minutes by using the MINUTE() function
87 Extract the year from a date value by using the YEAR() function
88 Extract the year part of the reference date and use normal arithmetic to add 10, 20, and 40 to it
89 Extracts the seconds from a time value
90 February 29 of leap years and March 1 of non-leap years appear to be the same day
91 Find the date a week ago
92 Find the player number, the year in which he or she joined the club, the town where he or she lives, and a cla
93 Finding Dates for Days of the Current Week
94 Finding Dates for Weekdays of Other Weeks
95 Finding First and Last Days of Months
96 Finding the Day in a Year
97 Finding the Day of the Week for a Date
98 Finding the Length of a Month
99 For an interval in weeks, do the same thing and divide the result by seven
100 For Christmas, replace the month and day with 12 and 25
101 For DATETIME- or TIMESTAMP-formatted strings, you can use DATE_ADD( ) to introduce a temporal context
102 For each match, get the time it starts, and get the time it starts plus eight hours
103 For each player whose number is less than 60, get the number of years between the year in which that player jo
104 For each player, find the player number, the year in which he or she joined the club, and the players age gro
105 Forcing MySQL to Treat Strings as Temporal Values
106 Format a date 1
107 Format a date 2
108 Format a date 3
109 Format a date 4
110 Format a date 5
111 Formatting Dates and Times
112 Formatting functions allow you to extract more than one part of a value
113 FROM_DAYS( ) converts the corresponding number of days to a date
114 FROM_UNIXTIMEUNIX_TIMESTAMP
115 Get Current time
116 Get day name, month name and day of year
117 Get day of month
118 Get Day of Month for a date
119 Get month of a date
120 Get sub date
121 Get the current full date object, current date nd time
122 Get the date seven days after the payment date
123 Get the day, month and year date components
124 Get the hour, minute and second time components
125 Get the name of the day
126 Get the name of the day and month on which they were born, and the days sequence number within the year of th
127 Get the number, the date of birth, and the date that comes seven days after that date of birth
128 Get the numbers of the players who were born in the same year as player 27
129 Get the payment number and the year of each penalty paid after 1980
130 Get the penalties that were paid between Christmas 1982 (December 25) and New Years Eve
131 Get the value of the TIME_ZONE system variable
132 Get the year of a date and compare
133 Get the YEAR part of the date
134 Get the Year value from subquery
135 Get year value from date type and compare
136 GET_FORMAT Formatting Codes
137 Group by year
138 Have birthdays next month
139 HAVING MAX(YEAR(PAYMENT_DATE)) = 1984
140 How old are the Smith children today
141 If you modify multiple records, the TIMESTAMP values in all of them will be updated
142 If you pass TIME_TO_SEC( ) a date-and-time value, it extracts the time part and discards the date
143 If you pass TO_DAYS( ) a date-and-time value, it extracts the date part and discards the time
144 Literal for timestamp type column
145 MySQL has other functions for returning just a part of a date
146 Obtain the current hour, minute, and second by passing CURTIME() or NOW( ) to a time-component function
147 Obtaining the Current Year, Month, Day, Hour, Minute, or Second
148 ON SCHEDULE EVERY 1 YEAR
149 Order date
150 Perform the week shift first
151 Performing Date Calculations
152 Performing Leap Year Calculations
153 Performing Range Tests 1
154 Performing Range Tests 2
155 Performing Range Tests 3
156 Present a date differently than in CCYY-MM-DD format or present a time without the seconds part
157 Pull out the entire date or time part from DATETIME values using string-extraction functions such as LEFT( ) o
158 Records created from 1 PM to 4 PM
159 Records modified within the last 12 hours
160 Retrieve the actual month name
161 Retrieve year from a date
162 Return the day of the week by number
163 Return the name of the day
164 Return the total working time, using SUM
165 Returns the number of workdays with ten or more hours
166 SEC_TO_TIME( ) converts the equivalent number of seconds to a TIME value
167 SEC_TO_TIME(MOD(TIME_TO_SEC(t1) + TIME_TO_SEC(t2), 86400))
168 SELECT @@GLOBAL DATETIME_FORMAT
169 SELECT DATE_FORMAT(2005-12-31, %D of %M)
170 SELECT DATE_FORMAT(2005-12-31, %M %d %Y)
171 SELECT DATE_FORMAT(2010-08-30 21
172 SELECT NOW( ), NOW( ) - INTERVAL 24 HOUR
173 SELECT TIME_FORMAT(02
174 SET @@SESSION DATETIME_FORMAT = DEFAULT
175 Shifting Dates by a Known Amount
176 Show current date
177 Some interval specifiers comprise both date and time parts
178 Sort by month, then by day within month
179 Sorting by Day of Week
180 SUBDATE(date, days) allows you to subtract a specified number of days from a date
181 Subtract from a date by using the SUBDATE() or DATE_SUB() functions
182 Synthesizing Dates or Times Using Component-Extraction Functions
183 Synthesizing Dates or Times Using Formatting Functions
184 Table shows the DAYOFWEEK( ) expressions to use for putting any day of the week first in the sort order
185 Table with TIMESTAMP data type
186 Telling MySQL How to Display Dates or Times
187 The ADDDATE() function includes a second form
188 The DATE and TIME data types use the following format
189 The datetime data types
190 The following query shows two ways to determine the date for Christmas two years hence
191 The INTERVAL clause requires an expression, which must be a time value in an acceptable format, and a type value
192 The INTERVAL() function compares the first integer listed as an argument to the integers that follow the first
193 The last day of the previous month is a special case for which the general expression can be simplified quite
194 The number of seconds between dates that lie two weeks apart can be computed like this
195 The string replacement technique can be used to produce dates with a specific position within the calendar year
196 The TIMESTAMP column type
197 TIME type column
198 TIME_FORMAT( )
199 TIME_FORMAT( ) understands time-related specifiers in the format string
200 TIME_TO_SEC( ) converts a TIME value to the equivalent number of seconds
201 TIME_TO_SEC()
202 TIMESTAMPADD is like ADDDATE, but it uses a different syntax for specifying the interval
203 TIMESTAMPDIFF returns the difference between two times as a multiple of the given interval
204 Time Symbols in DATE_FORMAT, TIME_FORMAT, and FROM_UNIXTIME
205 Time Zone Adjustments
206 To compute the total elapsed time, use TIME_TO_SEC( ) to convert the values to seconds before summing them
207 To convert the interval from seconds to other units, perform the appropriate division
208 To convert the interval in seconds to other units, perform the appropriate arithmetic operation
209 To convert the interval to a TIME value, pass it to SEC_TO_TIME( )
210 To ensure that the month has two digits-as required for ISO format-use LPAD( ) to add a leading zero as necess
211 To express time values as minutes, hours, or days, perform the appropriate divisions
212 To find out what date it is, according to the server, you can use the CURRENT_DATE() function
213 To find the earliest birthday within the calendar year, sort by the month and day of the birth values
214 To find the first day of the previous and following months relative to a given date, n would be -1 and 1
215 To get the day of the week as a number, use DAYOFWEEK( ) or WEEKDAY( )
216 To preserve accuracy with DATETIME or TIMESTAMP values, use UNIX_TIMESTAMP( ) and FROM_UNIXTIME( ) instead
217 To produce a day-of-week summary instead, use the DAYOFWEEK( ) function
218 To return the birthdays of all staff in the format MMDDYYYY, use the DATE_FORMAT() function
219 To shift a date forward or backward a week (seven days), use TO_DAYS( ) and FROM_DAYS( )
220 TO_DAYS( ) can convert DATETIME or TIMESTAMP values to days, if you dont mind having it chop off the time par
221 TO_DAYS( ) converts a date to the corresponding number of days
222 Transform the time differences into seconds using TIME_TO_SEC, add them, and then convert them back
223 Treating date-and-time as Numbers
224 Treating Dates or Times as Numbers
225 UNIX_TIMESTAMP calculation
226 UNIX_TIMESTAMP( ) can convert DATE values to seconds
227 Use BETWEEN and AND for a date data type
228 Use BETWEEN AND for date data type
229 Use date data type
230 Use DAYNAME( ) to display weekday names instead
231 Use DAYOFMONTH in a where clause
232 Use MONTH to get the month of a date
233 Use MONTH to get the month of a date 2
234 Use MONTHNAME in where clause
235 Use SEC_TO_TIME to make time more understandable
236 Use the + and - operators to perform date interval addition and subtraction
237 Use the ADDDATE() function to add 10 hours and 20 minutes to the specified datetime value
238 Use the EXTRACT( ) function to obtain individual parts of temporal values
239 Use the function TIMEDIFF
240 Use the YEAR() function
241 Use YEAR in where clause
242 Using DateTime Functions in Your SQL Statements
243 Using FROM_UNIXTIME
244 Using Leap Year Tests for Month-Length Calculations
245 Using Patterns with Non-String Values
246 Using TIME_TO_SEC( ) to strip off the date part of the t_create values
247 Using two date-and-time values that lie a week apart
248 WEEKDAY( ) returns values from 0 to 6, corresponding to Monday through Sunday
249 What time will it be in 60 hours
250 WHERE clause uses only part of the date column in the comparisons
251 WHERE DATE_ADD(d,INTERVAL 6 MONTH) = CURDATE( )
252 Year value in