Using MySQL Date and Time Functions, Part 2
Here is Part 2 of this tutorial for MySQL Date and Time Functions. Here are the links to Part 1 and 3:
- Part 1 of MySQL Date and Time Functions
- Part 3 of MySQL Date and Time Functions
Here is the list of MySQL Date and Time Functions for Part 2:
No. |
Name |
Description |
10 | DATE_FORMAT(date,format) | Formats the date value according to the format string.
The following specifiers may be used in the format string. The "%" character is required before format specifier characters.
Specifier |
Description |
%a |
Abbreviated weekday name (Sun..Sat) |
%b |
Abbreviated month name (Jan..Dec) |
%c |
Month, numeric (0..12) |
%D |
Day of the month with English suffix (0th, 1st, 2nd, 3rd, ...) |
%d |
Day of the month, numeric (00..31) |
%e |
Day of the month, numeric (0..31) |
%f |
Microseconds (000000..999999) |
%H |
Hour (00..23) |
%h |
Hour (01..12) |
%I |
Hour (01..12) |
%i |
Minutes, numeric (00..59) |
%j |
Day of year (001..366) |
%k |
Hour (0..23) |
%l |
Hour (1..12) |
%M |
Month name (January..December) |
%m |
Month, numeric (00..12) |
%p |
AM or
PM |
%r |
Time, 12-hour (hh:mm:ss followed by AM or PM) |
%S |
Seconds (00..59) |
%s |
Seconds (00..59) |
%T |
Time, 24-hour (hh:mm:ss) |
%U |
Week (00..53),
where Sunday is the first day of the week |
%u |
Week (00..53),
where Monday is the first day of the week |
%V |
Week (01..53),
where Sunday is the first day of the week; used with
%X |
%v |
Week (01..53),
where Monday is the first day of the week; used with
%x |
%W |
Weekday name (Sunday..Saturday) |
%w |
Day of the week (0=Sunday..6=Saturday) |
%X |
Year for the week where Sunday is the first day of
the week, numeric, four digits; used with
%V |
%x |
Year for the week, where Monday is the first day of
the week, numeric, four digits; used with
%v |
%Y |
Year, numeric, four digits |
%y |
Year, numeric (two digits) |
%% |
A literal "%" character |
%x |
x, for any "x" not listed above |
Practice #10-1
Practice #10-2
Practice #10-3
Practice #10-4
Practice #10-5
Practice #10-6
Practice #10-7
Practice #10-8
Practice #10-9
Practice #10-10
Practice #10-11
|
11 | DATE_SUB(date,INTERVAL expr unit) | Subtract two dates. See the description for DATE_ADD for similar usages for function arguments.
|
12 | DATE(expr) | Extracts the date part of the date or datetime expression expr.
See Practice #12.
|
13 | DATEDIFF(expr1,expr2) | Returns expr1 – expr2 expressed as a
value in days from one date to the other. expr1 and expr2 are
date or date-and-time expressions. Only the date part of the values are used in the calculation. Time part is ignored.
See Practice #13-1 and Practice #13-2. |
14 | DAY(date) | Synonym for DAYOFMONTH |
15 | DAYNAME(date) | Return the name of the weekday. See Practice #15.
|
16 | DAYOFMONTH(date) | Returns the day of the month for date, in the range 1 to 31, or 0 for dates such as '0000-00-00' or '2008-00-00' that have a zero day part.
See Practice #16.
|
17 | DAYOFWEEK(date) | Returns the weekday index for date (1 = Sunday, 2 = Monday, …, 7 = Saturday). These index values correspond to the ODBC standard. See Practice #17.
|
18 | DAYOFYEAR(date) | Returns the day of the year for date, in the range 1 to 366. See Practice #18.
|
19 | EXTRACT(unit FROM date) | This function uses the same kinds of unit specifiers as DATE_ADD, but extracts parts from the date rather than performing date arithmetic.
Practice #19-1
Practice #19-2
Practice #19-3
Practice #19-4
|
20 | FROM_DAYS(N) | Given a day number N, returns a DATE value. Day number starts from 366 which corresponds to 0001-01-01.
Practice #20-1
Practice #20-2
|
21 | FROM_UNIXTIME(unix_timestamp) FROM_UNIXTIME(unix_timestamp,format) | Returns a representation of the unix_timestamp argument as a value in
YYYY-MM-DD HH:MM:SS format (Practice #21-1) or YYYYMMDDHHMMSS.uuuuuu format (See Practice #21-2), depending on whether the function is used in
a string or numeric context. The value is expressed in the current time zone.
unix_timestamp is an internal timestamp value such as is produced by the UNIX_TIMESTAMP() function.
See more information about Unix timestamp in article How to use Date and Time data as integer value in PHP and MySQL.
If format is given, the result is formatted according to the format string, which is used the same
way as listed in the entry for the DATE_FORMAT function. See Practice #21-3. |
22 | GET_FORMAT(DATE|TIME|DATETIME, 'EUR'|'USA'|'JIS'|'ISO'|'INTERNAL') | Returns a format string such as %m.%d.%Y that can be used with other two functions - DATE_FORMAT and the STR_TO_DATE functions.
See Practice #22.
Here are the results of what you get when run the SELECT statements.
SELECT Statement |
Result |
SELECT GET_FORMAT(DATE,'USA'); |
%m.%d.%Y |
SELECT GET_FORMAT(DATE,'JIS'); |
%Y-%m-%d |
SELECT GET_FORMAT(DATE,'ISO'); |
%Y-%m-%d |
SELECT GET_FORMAT(DATE,'EUR'); |
%d.%m.%Y |
SELECT GET_FORMAT(DATE,'INTERNAL'); |
%Y%m%d |
SELECT GET_FORMAT(DATETIME,'USA'); |
%Y-%m-%d %H.%i.%s |
SELECT GET_FORMAT(DATETIME,'JIS'); |
%Y-%m-%d %H:%i:%s |
SELECT GET_FORMAT(DATETIME,'ISO'); |
%Y-%m-%d %H:%i:%s |
SELECT GET_FORMAT(DATETIME,'EUR'); |
%Y-%m-%d %H.%i.%s |
SELECT GET_FORMAT(DATETIME,'INTERNAL'); |
%Y%m%d%H%i%s |
SELECT GET_FORMAT(TIME,'USA'); |
%h:%i:%s %p |
SELECT GET_FORMAT(TIME,'JIS'); |
%H:%i:%s |
SELECT GET_FORMAT(TIME,'ISO'); |
%H:%i:%s |
SELECT GET_FORMAT(TIME,'EUR'); |
%H.%i.%s |
SELECT GET_FORMAT(TIME,'INTERNAL'); |
%H%i%s |
|
23 | HOUR(time) | Returns the hour part from a time string 'HH:MM:SS'. The range of the return value is 0 to 23 for time-of-day values. See Practice #23-1.
However, the range of TIME values actually is much larger, so HOUR can return values greater than 23. See Practice #23-2
|
24 | LAST_DAY(date) | Returns last day of the month for the date argument.
It takes a date or datetime value and returns the corresponding value for the last day of the month. See Practice #24-1
Returns NULL if the argument is invalid. See Practice #24-2
|
25 | LOCALTIME LOCALTIME() | Synonym for NOW
|
26 | LOCALTIMESTAMP LOCALTIMESTAMP() | Synonym for NOW |
27 | MAKEDATE(year,dayofyear) | Returns a date, given year and day of year values. dayofyear must be greater than 0 or the result is NULL. See Practice #27.
|
28 | MAKETIME(hour,minute,second) | Returns a time value calculated from the hour, minute, and second arguments. See Practice #28.
|
Practice #10-1: Using DATE_FORMAT with abbreviation
Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.
SELECT DATE_FORMAT('1997-10-04 22:23:00', '%a, %d %b %y');
Query result set - 1 row returned:
Practice #10-2: Using DATE_FORMAT without abbreviation
Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.
SELECT DATE_FORMAT('1997-10-04 22:23:00', '%W, %D %M %Y');
Query result set - 1 row returned:
Practice #10-3: Using DATE_FORMAT for American date format
Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.
SELECT DATE_FORMAT('1997-10-04 22:23:00', '%W, %m/%d/%Y');
Query result set - 1 row returned:
Practice #10-4: Using DATE_FORMAT to format hour, minute, second
Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.
SELECT DATE_FORMAT('1997-10-04 22:23:00', '%H:%i:%s');
SELECT DATE_FORMAT('1997-10-04 22:23:00', '%T');
Query result set - 1 row returned:
Practice #10-4: Using DATE_FORMAT to format hour, minute, second in 24-hour fashion
Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.
SELECT DATE_FORMAT('1997-10-04 22:23:00', '%H:%i:%s');
SELECT DATE_FORMAT('1997-10-04 22:23:00', '%T');
Query result set - 1 row returned:
Practice #10-5: Using DATE_FORMAT to format hour, minute, second in AM or PM fashion
Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.
SELECT DATE_FORMAT('1997-10-04 22:23:00', '%h:%i:%s %p');
SELECT DATE_FORMAT('1997-10-04 22:23:00', '%r');
Query result set - 1 row returned:
Practice #10-6: Using DATE_FORMAT to format microseconds
Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.
SELECT DATE_FORMAT('1997-10-04 22:23:00', '%f');
Query result set - 1 row returned:
Practice #10-7: Using DATE_FORMAT to get week number where Sunday is the first day
Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.
SELECT DATE_FORMAT('1997-10-04 22:23:00', '%U');
Query result set - 1 row returned:
Practice #10-8: Using DATE_FORMAT to get week number where Monday is the first day
Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.
SELECT DATE_FORMAT('1997-10-04 22:23:00', '%u');
Query result set - 1 row returned:
Practice #10-9: Using DATE_FORMAT to get day of the week (0=Sunday..6=Saturday)
Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.
SELECT DATE_FORMAT('1997-10-04 22:23:00', '%w');
Query result set - 1 row returned:
Practice #10-10: Using DATE_FORMAT to get week number and year, where Sunday is the first day
Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.
SELECT DATE_FORMAT('1997-10-04 22:23:00', 'Week %V of %X');
Query result set - 1 row returned:
Practice #10-11: Using DATE_FORMAT to get week number and year, where Monday is the first day
Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.
SELECT DATE_FORMAT('1997-10-04 22:23:00', 'Week %v of %x');
Query result set - 1 row returned:
Practice #12: Using DATE function
Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.
SELECT DATE('2003-12-31 01:02:03');
Query result set - 1 row returned:
Practice #13-1: Using DATEDIFF function to get a positive number
Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.
SELECT DATEDIFF('1997-12-31 23:59:59','1997-12-30');
Query result set - 1 row returned:
Practice #13-2: Using DATEDIFF function to get a negative number
Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.
SELECT DATEDIFF('1997-11-30 23:59:59','1997-12-31');
Query result set - 1 row returned:
Practice #15: Using DAYNAME function to get the name of the weekday
Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.
SELECT DAYNAME('1998-02-05');
Query result set - 1 row returned:
Practice #16: Using DAYOFMONTH function to get day of the month
Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.
SELECT DAYOFMONTH('1998-02-03');
Query result set - 1 row returned:
Practice #17: Using DAYOFWEEK function
Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.
SELECT DAYOFWEEK('1998-02-03');
Query result set - 1 row returned:
Practice #18: Using DAYOFYEAR function
Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.
SELECT DAYOFYEAR('1998-02-03');
Query result set - 1 row returned:
Practice #19-1: Using EXTRACT function to get year part
Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.
SELECT EXTRACT(YEAR FROM '1999-07-02');
Query result set - 1 row returned:
Practice #19-2: Using EXTRACT function to get year and month part
Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.
SELECT EXTRACT(YEAR_MONTH FROM '1999-07-02 01:02:03');
Query result set - 1 row returned:
Practice #19-3: Using EXTRACT function to get day, hour, minute
Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.
SELECT EXTRACT(DAY_MINUTE FROM '1999-07-02 01:02:03');
Query result set - 1 row returned:
Practice #19-4: Using EXTRACT function to get microsecond
Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.
SELECT EXTRACT(MICROSECOND FROM '2003-01-02 10:30:00.000123');
Query result set - 1 row returned:
Practice #20-1: Using FROM_DAYS function
Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.
Query result set - 1 row returned:
Practice #20-2: Using FROM_DAYS function
Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.
SELECT FROM_DAYS(729669);
Query result set - 1 row returned:
Practice #21-1: Using FROM_UNIXTIME function to convert Unix timestamp value to 'YYYY-MM-DD HH:MM:SS' format
Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.
SELECT FROM_UNIXTIME(1196440219);
Query result set - 1 row returned:
Practice #21-2: Using FROM_UNIXTIME function to convert Unix timestamp value to YYYYMMDDHHMMSS.uuuuuu format
Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.
SELECT FROM_UNIXTIME(1196440219) + 0;
Query result set - 1 row returned:
Practice #21-3: Using FROM_UNIXTIME function with format
Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.
SELECT FROM_UNIXTIME('2007-11-30 10:30:19',
'%D %M %Y %h:%i:%s %x') AS new_date;
Query result set - 1 row returned:
Practice #22: Using GET_FORMAT function with DATE_FORMAT function
Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.
SELECT DATE_FORMAT('2003-10-03',GET_FORMAT(DATE,'EUR'));
SELECT DATE_FORMAT('2003-10-03','%d.%m.%Y');
Query result set - 1 row returned:
Practice #23-1: Using HOUR function to get a value from 0 to 23.
Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.
Query result set - 1 row returned:
Practice #23-2: Using HOUR function to get a value greater than 23.
Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.
SELECT HOUR('272:59:59');
Query result set - 1 row returned:
Practice #24-1: Using LAST_DAY function.
Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.
SELECT LAST_DAY('2004-02-05');
Query result set - 1 row returned:
Practice #24-2: Using LAST_DAY function with invalid argument.
Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.
SELECT LAST_DAY('2003-03-32');
Query result set - 1 row returned:
Practice #27: Using MAKEDATE function.
Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.
SELECT MAKEDATE(2001,31), MAKEDATE(2001,32), MAKEDATE(2001,365);
Query result set - 1 row returned:
Practice #28: Using MAKETIME function.
Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.
SELECT MAKETIME(12,15,30);
Query result set - 1 row returned:
Other tutorials in this category
1. Using String Functions, Part 1
2. Using String Functions, Part 2
3. Using String Functions, Part 3
4. Using MySQL Date and Time Functions, Part 1
5. Using MySQL Date and Time Functions, Part 3
6. Using MySQL Control Flow Functions
7. Using MySQL Conversion Functions
8. Using MySQL Comparison Functions
9. Using Aggregate Functions in MySQL
10. MySQL RANK() function
11. MySQL DENSE_RANK() function
12. MySQL ROW_NUMBER() function
13. Compare RANK(), DENSE_RANK(), and ROW_NUMBER() function
14. Using LIMIT and OFFSET to Page Thru Query Result in MySQL
15. Using LIMIT, SQL_CALC_FOUND_ROWS and FOUND_ROWS() to Page Thru Query Result in MySQL
16. Use LEAST function to find the SMALLEST value from multiple arguments
17. How to make case-sensitive comparison in MySQL
18. Use GREATEST function to find the LARGEST value from multiple arguments
19. MySQL MID() function with examples
20. MySQL LOCATE() function with examples
21. MySQL SUBSTR() function with examples
22. MySQL POSITION() function with examples
23. How to use MySQL REPLACE function in SELECT and UPDATE queries
Back to Tutorial Index Page