MySQL Tutorial/Date Time Functions/DATE FORMAT

%a: Abbreviated weekday name (Sun..Sat)

mysql> SELECT DATE_FORMAT("1997-10-04 22:23:00", "%a");
+------------------------------------------+
| DATE_FORMAT("1997-10-04 22:23:00", "%a") |
+------------------------------------------+
| Sat                                      |
+------------------------------------------+
1 row in set (0.00 sec)
mysql>

%%: A literal "%" character

mysql>
mysql> SELECT DATE_FORMAT("1997-10-04 22:23:00", "%%");
+------------------------------------------+
| DATE_FORMAT("1997-10-04 22:23:00", "%%") |
+------------------------------------------+
| %                                        |
+------------------------------------------+
1 row in set (0.00 sec)
mysql>

%b: Abbreviated month name (Jan..Dec)

mysql> SELECT DATE_FORMAT("1997-10-04 22:23:00", "%b");
+------------------------------------------+
| DATE_FORMAT("1997-10-04 22:23:00", "%b") |
+------------------------------------------+
| Oct                                      |
+------------------------------------------+
1 row in set (0.00 sec)
mysql>

%c: Month, numeric (0..12)

mysql>
mysql> SELECT DATE_FORMAT("1997-10-04 22:23:00", "%c");
+------------------------------------------+
| DATE_FORMAT("1997-10-04 22:23:00", "%c") |
+------------------------------------------+
| 10                                       |
+------------------------------------------+
1 row in set (0.00 sec)

DATE_FORMAT()

DATE_FORMAT() function formats the date by specifying a sequence of format strings.

A string is composed of the percentage symbol "%" followed by a formatting letter.

These are some of the more common strings to use:

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

Upper and Lowercase letters in the string make a difference.

When arranging these strings into a sequence you can intersperse "normal" characters.

Syntax:

DATE_FORMAT(date, sequence)

Dates can also be formatted in "plain english"

DATE_FORMAT() only works with datatypes that include the date, which inlcudes DATE, DATETIME and TIMESTAMP.

There is a similar function called TIME_FORMAT() that works with TIME as well as DATETIME and TIMESTAMP.

mysql>
mysql> CREATE TABLE Employee(
    ->     id            int,
    ->     first_name    VARCHAR(15),
    ->     last_name     VARCHAR(15),
    ->     start_date    DATE,
    ->     end_date      DATE,
    ->     salary        FLOAT(8,2),
    ->     city          VARCHAR(10),
    ->     description   VARCHAR(15)
    -> );
Query OK, 0 rows affected (0.03 sec)
mysql>
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->              values (1,"Jason",    "Martin",  "19960725",  "20060725", 1234.56, "Toronto",  "Programmer");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(2,"Alison",   "Mathews",  "19760321", "19860221", 6661.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(3,"James",    "Smith",    "19781212", "19900315", 6544.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(4,"Celia",    "Rice",     "19821024", "19990421", 2344.78, "Vancouver","Manager");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(5,"Robert",   "Black",    "19840115", "19980808", 2334.78, "Vancouver","Tester");
Query OK, 1 row affected (0.02 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(6,"Linda",    "Green",    "19870730", "19960104", 4322.78,"New York",  "Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(7,"David",    "Larry",    "19901231", "19980212", 7897.78,"New York",  "Manager");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(8,"James",    "Cat",     "19960917",  "20020415", 1232.78,"Vancouver", "Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> select * from Employee;
+------+------------+-----------+------------+------------+---------+-----------+-------------+
| id   | first_name | last_name | start_date | end_date   | salary  | city      | description |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
|    1 | Jason      | Martin    | 1996-07-25 | 2006-07-25 | 1234.56 | Toronto   | Programmer  |
|    2 | Alison     | Mathews   | 1976-03-21 | 1986-02-21 | 6661.78 | Vancouver | Tester      |
|    3 | James      | Smith     | 1978-12-12 | 1990-03-15 | 6544.78 | Vancouver | Tester      |
|    4 | Celia      | Rice      | 1982-10-24 | 1999-04-21 | 2344.78 | Vancouver | Manager     |
|    5 | Robert     | Black     | 1984-01-15 | 1998-08-08 | 2334.78 | Vancouver | Tester      |
|    6 | Linda      | Green     | 1987-07-30 | 1996-01-04 | 4322.78 | New York  | Tester      |
|    7 | David      | Larry     | 1990-12-31 | 1998-02-12 | 7897.78 | New York  | Manager     |
|    8 | James      | Cat       | 1996-09-17 | 2002-04-15 | 1232.78 | Vancouver | Tester      |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
8 rows in set (0.02 sec)
mysql>
mysql> SELECT first_name, DATE_FORMAT(start_date, "%W the %D of %M %Y")
    -> FROM employee;
+------------+-----------------------------------------------+
| first_name | DATE_FORMAT(start_date, "%W the %D of %M %Y") |
+------------+-----------------------------------------------+
| Jason      | Thursday the 25th of July 1996                |
| Alison     | Sunday the 21st of March 1976                 |
| James      | Tuesday the 12th of December 1978             |
| Celia      | Sunday the 24th of October 1982               |
| Robert     | Sunday the 15th of January 1984               |
| Linda      | Thursday the 30th of July 1987                |
| David      | Monday the 31st of December 1990              |
| James      | Tuesday the 17th of September 1996            |
+------------+-----------------------------------------------+
8 rows in set (0.00 sec)
mysql>
mysql> drop table Employee;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql>

%d: Day of the month, numeric (00..31)

mysql>
mysql>
mysql> SELECT DATE_FORMAT("1997-10-04 22:23:00", "%a");
+------------------------------------------+
| DATE_FORMAT("1997-10-04 22:23:00", "%a") |
+------------------------------------------+
| Sat                                      |
+------------------------------------------+
1 row in set (0.00 sec)
mysql>

%D: Day of the month with English suffix (0th, 1st, 2nd, 3rd, ...)

mysql>
mysql> SELECT DATE_FORMAT("1997-10-04 22:23:00", "%D");
+------------------------------------------+
| DATE_FORMAT("1997-10-04 22:23:00", "%D") |
+------------------------------------------+
| 4th                                      |
+------------------------------------------+
1 row in set (0.00 sec)
mysql>

%e: Day of the month, numeric (0..31)

mysql>
mysql> SELECT DATE_FORMAT("1997-10-04 22:23:00", "%e");
+------------------------------------------+
| DATE_FORMAT("1997-10-04 22:23:00", "%e") |
+------------------------------------------+
| 4                                        |
+------------------------------------------+
1 row in set (0.00 sec)
mysql>

%f: Microseconds (000000..999999)

mysql>
mysql> SELECT DATE_FORMAT("1997-10-04 22:23:00", "%f");
+------------------------------------------+
| DATE_FORMAT("1997-10-04 22:23:00", "%f") |
+------------------------------------------+
| 000000                                   |
+------------------------------------------+
1 row in set (0.00 sec)
mysql>

%H: Hour (00..23)

mysql>
mysql> SELECT DATE_FORMAT("1997-10-04 22:23:00", "%H");
+------------------------------------------+
| DATE_FORMAT("1997-10-04 22:23:00", "%H") |
+------------------------------------------+
| 22                                       |
+------------------------------------------+
1 row in set (0.00 sec)

%h: Hour (01..12)

mysql>
mysql>
mysql> SELECT DATE_FORMAT("1997-10-04 22:23:00", "%h");
+------------------------------------------+
| DATE_FORMAT("1997-10-04 22:23:00", "%h") |
+------------------------------------------+
| 10                                       |
+------------------------------------------+
1 row in set (0.00 sec)

%I: Hour (01..12)

mysql>
mysql>
mysql> SELECT DATE_FORMAT("1997-10-04 22:23:00", "%I");
+------------------------------------------+
| DATE_FORMAT("1997-10-04 22:23:00", "%I") |
+------------------------------------------+
| 10                                       |
+------------------------------------------+
1 row in set (0.00 sec)
mysql>

%i: Minutes, numeric (00..59)

mysql>
mysql> SELECT DATE_FORMAT("1997-10-04 22:23:00", "%i");
+------------------------------------------+
| DATE_FORMAT("1997-10-04 22:23:00", "%i") |
+------------------------------------------+
| 23                                       |
+------------------------------------------+
1 row in set (0.00 sec)

%j: Day of year (001..366)

mysql>
mysql> SELECT DATE_FORMAT("1997-10-04 22:23:00", "%j");
+------------------------------------------+
| DATE_FORMAT("1997-10-04 22:23:00", "%j") |
+------------------------------------------+
| 277                                      |
+------------------------------------------+
1 row in set (0.00 sec)

%k: Hour (0..23)

mysql>
mysql> SELECT DATE_FORMAT("1997-10-04 22:23:00", "%k");
+------------------------------------------+
| DATE_FORMAT("1997-10-04 22:23:00", "%k") |
+------------------------------------------+
| 22                                       |
+------------------------------------------+
1 row in set (0.00 sec)

%l: Hour (1..12)

mysql>
mysql> SELECT DATE_FORMAT("1997-10-04 22:23:00", "%l");
+------------------------------------------+
| DATE_FORMAT("1997-10-04 22:23:00", "%l") |
+------------------------------------------+
| 10                                       |
+------------------------------------------+
1 row in set (0.00 sec)

%M: Month name (January..December)

mysql>
mysql> SELECT DATE_FORMAT("1997-10-04 22:23:00", "%M");
+------------------------------------------+
| DATE_FORMAT("1997-10-04 22:23:00", "%M") |
+------------------------------------------+
| October                                  |
+------------------------------------------+
1 row in set (0.00 sec)

%m: Month, numeric (00..12)

mysql>
mysql> SELECT DATE_FORMAT("1997-10-04 22:23:00", "%m");
+------------------------------------------+
| DATE_FORMAT("1997-10-04 22:23:00", "%m") |
+------------------------------------------+
| 10                                       |
+------------------------------------------+
1 row in set (0.00 sec)

%p: AM or PM

mysql>
mysql> SELECT DATE_FORMAT("1997-10-04 22:23:00", "%p");
+------------------------------------------+
| DATE_FORMAT("1997-10-04 22:23:00", "%p") |
+------------------------------------------+
| PM                                       |
+------------------------------------------+
1 row in set (0.00 sec)

%r: Time, 12-hour (hh:mm:ss followed by AM or PM)

mysql>
mysql> SELECT DATE_FORMAT("1997-10-04 22:23:00", "%r");
+------------------------------------------+
| DATE_FORMAT("1997-10-04 22:23:00", "%r") |
+------------------------------------------+
| 10:23:00 PM                              |
+------------------------------------------+
1 row in set (0.00 sec)

SELECT DATE_FORMAT("1997-23:00","%D %y %a %d %m %b %j");

mysql>
mysql> SELECT DATE_FORMAT("1997-10-04 22:23:00","%D %y %a %d %m %b %j");
+-----------------------------------------------------------+
| DATE_FORMAT("1997-10-04 22:23:00","%D %y %a %d %m %b %j") |
+-----------------------------------------------------------+
| 4th 97 Sat 04 10 Oct 277                                  |
+-----------------------------------------------------------+
1 row in set (0.01 sec)
mysql>

SELECT DATE_FORMAT("1997-23:00", "%H:%i:%s");

mysql>
mysql> SELECT DATE_FORMAT("1997-10-04 22:23:00", "%H:%i:%s");
+------------------------------------------------+
| DATE_FORMAT("1997-10-04 22:23:00", "%H:%i:%s") |
+------------------------------------------------+
| 22:23:00                                       |
+------------------------------------------------+
1 row in set (0.00 sec)
mysql>

SELECT DATE_FORMAT("1997-23:00","%H %k %I %r %T %S %w");

mysql>
mysql>
mysql> SELECT DATE_FORMAT("1997-10-04 22:23:00","%H %k %I %r %T %S %w");
+-----------------------------------------------------------+
| DATE_FORMAT("1997-10-04 22:23:00","%H %k %I %r %T %S %w") |
+-----------------------------------------------------------+
| 22 22 10 10:23:00 PM 22:23:00 00 6                        |
+-----------------------------------------------------------+
1 row in set (0.00 sec)
mysql>

SELECT DATE_FORMAT("1997-23:00", "%W %M %Y");

mysql>
mysql> SELECT DATE_FORMAT("1997-10-04 22:23:00", "%W %M %Y");
+------------------------------------------------+
| DATE_FORMAT("1997-10-04 22:23:00", "%W %M %Y") |
+------------------------------------------------+
| Saturday October 1997                          |
+------------------------------------------------+
1 row in set (0.00 sec)
mysql>

SELECT DATE_FORMAT("1999-01-01", "%X %V");

mysql>
mysql> SELECT DATE_FORMAT("1999-01-01", "%X %V");
+------------------------------------+
| DATE_FORMAT("1999-01-01", "%X %V") |
+------------------------------------+
| 1998 52                            |
+------------------------------------+
1 row in set (0.00 sec)
mysql>

SELECT DATE_FORMAT("2006-06-00", "%d");

mysql>
mysql> SELECT DATE_FORMAT("2006-06-00", "%d");
+---------------------------------+
| DATE_FORMAT("2006-06-00", "%d") |
+---------------------------------+
| 00                              |
+---------------------------------+
1 row in set (0.00 sec)
mysql>

%S: Seconds (00..59)

mysql>
mysql> SELECT DATE_FORMAT("1997-10-04 22:23:00", "%S");
+------------------------------------------+
| DATE_FORMAT("1997-10-04 22:23:00", "%S") |
+------------------------------------------+
| 00                                       |
+------------------------------------------+
1 row in set (0.00 sec)

%T: Time, 24-hour (hh:mm:ss)

mysql>
mysql> SELECT DATE_FORMAT("1997-10-04 22:23:00", "%T");
+------------------------------------------+
| DATE_FORMAT("1997-10-04 22:23:00", "%T") |
+------------------------------------------+
| 22:23:00                                 |
+------------------------------------------+
1 row in set (0.00 sec)

Using DATE_FORMAT() with an alias to tidy up the title

mysql>
mysql> SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 6 MONTH), "%W the %D of %M %Y")
    -> AS "Six Months Ago";
+---------------------------------+
| Six Months Ago                  |
+---------------------------------+
| Sunday the 21st of January 2007 |
+---------------------------------+
1 row in set (0.00 sec)
mysql>
mysql>
mysql>

%u: Week (00..53), where Monday is the first day of the week

mysql>
mysql> SELECT DATE_FORMAT("1997-10-04 22:23:00", "%u");
+------------------------------------------+
| DATE_FORMAT("1997-10-04 22:23:00", "%u") |
+------------------------------------------+
| 40                                       |
+------------------------------------------+
1 row in set (0.00 sec)

%U: Week (00..53), where Sunday is the first day of the week

mysql>
mysql> SELECT DATE_FORMAT("1997-10-04 22:23:00", "%U");
+------------------------------------------+
| DATE_FORMAT("1997-10-04 22:23:00", "%U") |
+------------------------------------------+
| 39                                       |
+------------------------------------------+
1 row in set (0.00 sec)

%v: Week (01..53), where Monday is the first day of the week

mysql>
mysql> SELECT DATE_FORMAT("1997-10-04 22:23:00", "%v");
+------------------------------------------+
| DATE_FORMAT("1997-10-04 22:23:00", "%v") |
+------------------------------------------+
| 40                                       |
+------------------------------------------+
1 row in set (0.00 sec)

%V: Week (01..53), where Sunday is the first day of the week; used with %X

mysql>
mysql> SELECT DATE_FORMAT("1997-10-04 22:23:00", "%V");
+------------------------------------------+
| DATE_FORMAT("1997-10-04 22:23:00", "%V") |
+------------------------------------------+
| 39                                       |
+------------------------------------------+
1 row in set (0.00 sec)

%w: Day of the week (0=Sunday..6=Saturday)

mysql>
mysql> SELECT DATE_FORMAT("1997-10-04 22:23:00", "%w");
+------------------------------------------+
| DATE_FORMAT("1997-10-04 22:23:00", "%w") |
+------------------------------------------+
| 6                                        |
+------------------------------------------+
1 row in set (0.00 sec)

%W: Weekday name (Sunday..Saturday)

mysql>
mysql> SELECT DATE_FORMAT("1997-10-04 22:23:00", "%W");
+------------------------------------------+
| DATE_FORMAT("1997-10-04 22:23:00", "%W") |
+------------------------------------------+
| Saturday                                 |
+------------------------------------------+
1 row in set (0.00 sec)

%x: Year for the week, where Monday is the first day of the week, numeric, four digits

mysql>
mysql> SELECT DATE_FORMAT("1997-10-04 22:23:00", "%x");
+------------------------------------------+
| DATE_FORMAT("1997-10-04 22:23:00", "%x") |
+------------------------------------------+
| 1997                                     |
+------------------------------------------+
1 row in set (0.00 sec)

%X: Year for the week where Sunday is the first day of the week, numeric, four digits

mysql>
mysql> SELECT DATE_FORMAT("1997-10-04 22:23:00", "%X");
+------------------------------------------+
| DATE_FORMAT("1997-10-04 22:23:00", "%X") |
+------------------------------------------+
| 1997                                     |
+------------------------------------------+
1 row in set (0.00 sec)

%Y: Year, numeric, four digits

mysql>
mysql> SELECT DATE_FORMAT("1997-10-04 22:23:00", "%Y");
+------------------------------------------+
| DATE_FORMAT("1997-10-04 22:23:00", "%Y") |
+------------------------------------------+
| 1997                                     |
+------------------------------------------+
1 row in set (0.00 sec)

%y: Year, numeric (two digits)

mysql>
mysql> SELECT DATE_FORMAT("1997-10-04 22:23:00", "%y");
+------------------------------------------+
| DATE_FORMAT("1997-10-04 22:23:00", "%y") |
+------------------------------------------+
| 97                                       |
+------------------------------------------+
1 row in set (0.00 sec)