MySQL Tutorial Date Time Functions 269 codes

  1. Home
  2. MySQL Tutorial
  3. Date Time Functions
1 %%
2 %a
3 %b
4 %c
5 %D
6 %e
7 %f
8 %H
9 %I
10 %j
11 %k
12 %l
13 %M
14 %p
15 %r
16 %S
17 %T
18 %U
19 %V
20 %W
21 %X
22 %Y
23 ADDDATE(date,INTERVAL expr unit)
24 ADDDATE(expr,days)
25 Adding one day to a date
26 Adding one month to a date
27 Adding one month to start date
28 Adding one year to a date
29 ADDTIME(01
30 ADDTIME(expr1,expr2)
31 An illegal date returns a NULL result
32 Changing Date Values
33 Combine Month function and Date_add function
34 CONVERT_TZ(dt,from_tz,to_tz)
35 CURDATE()
36 Current Date and Time
37 CURRENT_TIMESTAMP() is the synonym for NOW()
38 CURTIME() returns the current time as a value in HH
39 Date and Time Functions
40 Date arithmetic also can be performed using INTERVAL together with the + or - operator
41 Date arithmetic operations require complete dates
42 DATE(expr) Extracts the date part of the date or datetime expression expr
43 DATE_ADD(curdate(), INTERVAL 1
44 DATE_ADD(curdate(), INTERVAL 1 1
45 DATE_ADD(curdate(), INTERVAL 1 1 DAY_HOUR);(wihtout
46 DATE_ADD(curdate(), INTERVAL 1 1 DAY_MICROSECOND)
47 DATE_ADD(curdate(), INTERVAL 1 1 HOUR_MICROSECOND)
48 DATE_ADD(curdate(), INTERVAL 1 1 HOUR_MINUTE); (with dot)
49 DATE_ADD(curdate(), INTERVAL 1 1 HOUR_SECOND)
50 DATE_ADD(curdate(), INTERVAL 1 1 MINUTE_MICROSECOND)
51 DATE_ADD(curdate(), INTERVAL 1 1 MINUTE_SECOND)
52 DATE_ADD(curdate(), INTERVAL 1 1 SECOND)
53 DATE_ADD(curdate(), INTERVAL 1 1 SECOND_MICROSECOND)
54 DATE_ADD(curdate(), INTERVAL 1 1 SECOND_MICROSECOND); (with dot)
55 DATE_ADD(curdate(), INTERVAL 1 1 YEAR_MONTH); (with dot)
56 DATE_ADD(curdate(), INTERVAL 1 DAY)
57 DATE_ADD(curdate(), INTERVAL 1 HOUR)
58 DATE_ADD(curdate(), INTERVAL 1 MICROSECOND)
59 DATE_ADD(curdate(), INTERVAL 1 MINUTE)
60 DATE_ADD(curdate(), INTERVAL 1 MONTH)
61 DATE_ADD(curdate(), INTERVAL 1 QUARTER)
62 DATE_ADD(curdate(), INTERVAL 1 SECOND)
63 DATE_ADD(curdate(), INTERVAL 1 WEEK)
64 DATE_ADD(curdate(), INTERVAL 1 YEAR)
65 DATE_ADD(curdate(), INTERVAL 1-1 YEAR_MONTH); (with minus sign)
66 DATE_ADD(date,INTERVAL expr unit)
67 DATE_FORMAT()
68 DATE_SUB(curdate(), INTERVAL 1 1 MINUTE_MICROSECOND)
69 DATE_SUB(curdate(), INTERVAL 1 1 MINUTE_SECOND)
70 DATE_SUB(curdate(), INTERVAL 1 1 SECOND)
71 DATE_SUB(curdate(), INTERVAL 1 1 SECOND_MICROSECOND)
72 DATE_SUB(curdate(), INTERVAL 1 DAY)
73 DATE_SUB(curdate(), INTERVAL 1 HOUR)
74 DATE_SUB(curdate(), INTERVAL 1 MICROSECOND)
75 DATE_SUB(curdate(), INTERVAL 1 MINUTE)
76 DATE_SUB(curdate(), INTERVAL 1 MONTH)
77 DATE_SUB(curdate(), INTERVAL 1 QUARTER)
78 DATE_SUB(curdate(), INTERVAL 1 SECOND)
79 DATE_SUB(curdate(), INTERVAL 1 WEEK)
80 DATE_SUB(curdate(), INTERVAL 1 YEAR)
81 DATE_SUB(date,INTERVAL expr unit)
82 DATEDIFF(expr1,expr2)
83 Dates can also be formatted in plain english
84 DAYNAME(2006-05-00)
85 DAYNAME(date) returns the name of the weekday for date
86 DAYOFMONTH(2001-11-00)
87 DAYOFMONTH(date) returns the day of the month for a given date
88 DAYOFMONTH(date) returns the day of the month for date, in the range 0 to 31
89 DAYOFWEEK(date) returns the day of the week for a given date, starting with 1=Sunday, 2=Monday, 3=Tuesday, and so on.t
90 DAYOFWEEK(date) returns the weekday index for date (1 = Sunday, 2 = Monday, , 7 = Saturday)
91 DAYOFYEAR(date) returns the day of the year for date, in the range 1 to 366
92 Days Since 1 A D
93 Deal with two-digit-year value
94 EXTRACT(unit FROM date)
95 Extracting Years, Quarters, Months, and Weeks
96 Extraction Functions
97 FROM_DAYS(N)
98 FROM_UNIXTIME(unix_timestamp), FROM_UNIXTIME(unix_timestamp,format)
99 Get DAYNAME of employees start date
100 Get DAYOFMONTH of start date
101 Get Month name of the employees start date
102 GET_FORMAT(DATE,EUR)
103 GET_FORMAT(DATE,INTERNAL)
104 GET_FORMAT(DATE,ISO)
105 GET_FORMAT(DATE,JIS)
106 GET_FORMAT(DATE,USA)
107 GET_FORMAT(DATETIME,EUR)
108 GET_FORMAT(DATETIME,INTERNAL)
109 GET_FORMAT(DATETIME,ISO)
110 GET_FORMAT(DATETIME,JIS)
111 GET_FORMAT(DATETIME,USA)
112 GET_FORMAT(DATETIMEDATETIME, EURUSAJISISOINTERNAL)
113 GET_FORMAT(TIME,EUR)
114 GET_FORMAT(TIME,INTERNAL)
115 GET_FORMAT(TIME,ISO)
116 GET_FORMAT(TIME,JIS)
117 GET_FORMAT(TIME,USA)
118 Getting a date 6 months in the past
119 Getting Day Information
120 Getting Names for Months and Days
121 Getting the Current Date and Time
122 HOUR(time) returns the hour for time
123 If the resulting date has a day that is larger than the maximum day for the new month, the day is adjusted to the maxi
124 If you add to or subtract from a date value something that contains a time part, the result is automatically converted
125 LAST_DAY(date) returns the corresponding value for the last day of the month
126 LOCALTIME() is the synonym for NOW()
127 LOCALTIMESTAMP is the synonym for NOW()
128 MAKEDATE(year,dayofyear) returns a date, given year and day-of-year values
129 MAKETIME(hour,minute,second) returns a time value calculated from the hour, minute, and second arguments
130 MICROSECOND(expr) returns the microseconds as a number in the range from 0 to 999999
131 MINUTE(time) returns the minute for time, in the range 0 to 59
132 MONTH(2005-00-00)
133 MONTH(date) returns the month for date, in the range 0 to 12
134 MONTHNAME(date) returns the full name of the month for date
135 Multiple references to a function such as NOW() within a single query always produce the same result
136 Pass DAYOFYEAR a date as a string parameter
137 PERIOD_ADD(P,N) adds N months to period P (in the format YYMM or YYYYMM)
138 PERIOD_DIFF(P1,P2) returns the number of months between periods P1 and P2
139 QUARTER(date) returns the quarter of the year for date, in the range 1 to 4
140 QUARTER(date) the range 1 to 4
141 SEC_TO_TIME(seconds) returns the seconds argument, converted to hours, minutes, and seconds
142 SECOND(time) returns the second for time, in the range 0 to 59
143 Seconds Since the Beginning of the Day
144 SELECT 1998-01-01 - INTERVAL 1 SECOND
145 SELECT 2005-03-32 + INTERVAL 1 MONTH
146 SELECT CONVERT_TZ(2004-01-01 12
147 SELECT DATE_ADD(1992-12-31 23
148 SELECT DATE_ADD(1997-12-31 23
149 SELECT DATE_ADD(1998-01-01 00
150 SELECT DATE_ADD(1999-01-01, INTERVAL 1 HOUR)
151 SELECT DATE_FORMAT(1997-10-04 22
152 SELECT DATE_FORMAT(1999-01-01, %X %V)
153 SELECT DATE_FORMAT(2003-10-03,GET_FORMAT(DATE,EUR))
154 SELECT DATE_FORMAT(2006-06-00, %d)
155 SELECT DATE_SUB(1998-01-01 00
156 SELECT DATE_SUB(1998-01-02, INTERVAL 31 DAY)
157 Select DATE_SUB(curdate(), INTERVAL 1
158 Select DATE_SUB(curdate(), INTERVAL 1 1
159 Select DATE_SUB(curdate(), INTERVAL 1 1 DAY_HOUR)
160 Select DATE_SUB(curdate(), INTERVAL 1 1 DAY_MICROSECOND)
161 Select DATE_SUB(curdate(), INTERVAL 1 1 HOUR_MICROSECOND)
162 Select DATE_SUB(curdate(), INTERVAL 1 1 HOUR_MINUTE); (with dot)
163 Select DATE_SUB(curdate(), INTERVAL 1 1 HOUR_SECOND)
164 Select DATE_SUB(curdate(), INTERVAL 1 1 SECOND_MICROSECOND)
165 Select DATE_SUB(curdate(), INTERVAL 1 1 YEAR_MONTH); (with dot)
166 Select DATE_SUB(curdate(), INTERVAL 1-1 YEAR_MONTH)
167 SELECT DAYNAME(2000-01-01)
168 SELECT EXTRACT(DAY FROM 2003-01-02 10
169 SELECT EXTRACT(DAY_HOUR FROM 2003-01-02 10
170 SELECT EXTRACT(DAY_MICROSECOND FROM 2003-01-02 10
171 SELECT EXTRACT(DAY_MINUTE FROM 1999-07-02 01
172 SELECT EXTRACT(DAY_SECOND FROM 2003-01-02 10
173 SELECT EXTRACT(HOUR FROM 2003-01-02 10
174 SELECT EXTRACT(HOUR_MICROSECOND FROM 2003-01-02 10
175 SELECT EXTRACT(HOUR_MINUTE FROM 2003-01-02 10
176 SELECT EXTRACT(HOUR_SECOND FROM 2003-01-02 10
177 SELECT EXTRACT(MICROSECOND FROM 2003-01-02 10
178 SELECT EXTRACT(MINUTE FROM 2003-01-02 10
179 SELECT EXTRACT(MINUTE_MICROSECOND FROM 2003-01-02 10
180 SELECT EXTRACT(MINUTE_SECOND FROM 2003-01-02 10
181 SELECT EXTRACT(MONTH FROM 2003-01-02 10
182 SELECT EXTRACT(QUARTER FROM 2003-01-02 10
183 SELECT EXTRACT(SECOND FROM 2003-01-02 10
184 SELECT EXTRACT(SECOND_MICROSECOND FROM 2003-01-02 10
185 SELECT EXTRACT(WEEK FROM 2003-01-02 10
186 SELECT EXTRACT(YEAR FROM 1999-07-02)
187 SELECT EXTRACT(YEAR_MONTH FROM 1999-07-02 01
188 SELECT FROM_UNIXTIME(1111885200)
189 SELECT INTERVAL 1 DAY + 1997-12-31
190 SELECT LAST_DAY(2003-03-32)
191 SELECT LAST_DAY(2004-01-01 01
192 SELECT LAST_DAY(2004-02-05)
193 SELECT MAKEDATE(2001,0)
194 Select MAKEDATE(2001,32)
195 SELECT MAKEDATE(2001,365)
196 Select MAKEDATE(2004,365)
197 SELECT MICROSECOND(1997-12-31 23
198 SELECT MID(YEARWEEK(2000-01-01),5,2)
199 SELECT MONTHNAME(1989-07-20)
200 SELECT NOW(), SLEEP(2), NOW()
201 SELECT STR_TO_DATE(00000000, %m%d%Y)
202 SELECT STR_TO_DATE(04312004, %m%d%Y)
203 SELECT STR_TO_DATE(10 31 2003,GET_FORMAT(DATE,USA))
204 SELECT SUBDATE(1998-01-02, INTERVAL 31 DAY)
205 SELECT SUBTIME(01
206 SELECT SYSDATE(), SLEEP(2), SYSDATE()
207 SELECT TIME(2003-12-31 01
208 SELECT TIME_TO_SEC(00
209 SELECT TIMEDIFF(1997-12-31 23
210 SELECT TIMESTAMPADD(DAY,1,2003-01-02)
211 SELECT TIMESTAMPADD(FRAC_SECOND,1,2003-01-02)
212 SELECT TIMESTAMPADD(HOUR,1,2003-01-02)
213 SELECT TIMESTAMPADD(MONTH,1,2003-01-02)
214 SELECT TIMESTAMPADD(QUARTER,1,2003-01-02)
215 SELECT TIMESTAMPADD(SECOND,1,2003-01-02)
216 SELECT TIMESTAMPADD(WEEK,1,2003-01-02)
217 SELECT TIMESTAMPADD(YEAR,1,2003-01-02)
218 SELECT TIMESTAMPDIFF(DAY,2003-02-01,2003-05-01)
219 SELECT TIMESTAMPDIFF(FRAC_SECOND,2003-02-01,2003-05-01)
220 SELECT TIMESTAMPDIFF(HOUR,2003-02-01,2003-05-01)
221 SELECT TIMESTAMPDIFF(MINUTE,2003-02-01,2003-05-01)
222 SELECT TIMESTAMPDIFF(MONTH,2003-02-01,2003-05-01)
223 SELECT TIMESTAMPDIFF(QUARTER,2003-02-01,2003-05-01)
224 SELECT TIMESTAMPDIFF(SECOND,2003-02-01,2003-05-01)
225 SELECT TIMESTAMPDIFF(WEEK,2003-02-01,2003-05-01)
226 SELECT TIMESTAMPDIFF(YEAR,2002-05-01,2001-01-01)
227 SELECT TIMESTAMPDIFF(YEAR,2003-02-01,2003-05-01)
228 SELECT TO_DAYS(1997-10-07)
229 SELECT UNIX_TIMESTAMP(2005-03-27 03
230 SELECT WEEKDAY(1997-11-05)
231 Select YEAR(1973-07-20)
232 SELECT YEARWEEK(2000-01-01)
233 Selects all rows with a start_date value from within the last 30 days
234 STR_TO_DATE(str,format)
235 SUBDATE(date,INTERVAL expr unit)
236 SUBDATE(expr,days)
237 SUBTIME(expr1,expr2)
238 SYSDATE() returns the current date and time
239 TIME(expr) extracts the time part of the time or datetime expression expr and returns it as a string
240 TIME_FORMAT(time,format)
241 TIME_TO_SEC(time) returns the time argument, converted to seconds
242 TIMEDIFF(expr1,expr2) returns expr1 C expr2 expressed as a time value
243 TIMESTAMP(expr) returns the date or datetime expression expr as a datetime value
244 TIMESTAMP(expr1,expr2) adds the time expression expr2 to the date or datetime expression expr1 and returns the result
245 TIMESTAMPADD(unit,interval,datetime_expr) adds the integer expression interval to datetime_expr
246 TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)
247 To convert a year-week to a date, then you should also specify the weekday
248 TO_DAYS(date) returns a day number (the number of days since year 0)
249 UNIX_TIMESTAMP() returns a Unix timestamp (seconds since 1970-01-01 00
250 UNIX_TIMESTAMP(date) returns the value of the argument as seconds since 1970-01-01 00
251 Use month function in where clause
252 Use month function with data in a table
253 Using CURDATE() function
254 Using CURTIME() function
255 Using DATE_FORMAT() with an alias to tidy up the title
256 Using DATE_SUB function to substract 55 years from current date
257 Using DATEDIFF with data in a column
258 UTC_DATE() returns the current UTC date
259 UTC_TIME() returns the current UTC time
260 UTC_TIMESTAMP() returns the current UTC date and time
261 WEEK(date), parameter, returns the week of the year in the range 0 to 53
262 WEEK(date[,mode]) returns the week number for date
263 WEEKDAY(date) different and returns the weekday index for the given date(0=Monday, 1=Tuesday, 2=Wednesday, and so on).
264 WEEKDAY(date) returns the weekday index for date (0 = Monday, 1 = Tuesday, 6 = Sunday)
265 WEEKOFYEAR(date) returns the calendar week of the date as a number in the range from 1 to 53
266 YEAR(date) returns the 4-digit numeric year of a given date
267 YEAR(date) returns the year for date, in the range 1000 to 9999, or 0 for the zero date
268 YEARWEEK(date), YEARWEEK(date,mode) returns year and week for a date
269 You can optionally give FROM_UNIXTIME function a format parameter