MySQL Tutorial/Date Time Functions/DATE FORMAT

Материал из SQL эксперт
Перейти к: навигация, поиск

Содержание

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

   <source lang="sql">

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></source>


%%: A literal "%" character

   <source lang="sql">

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></source>


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

   <source lang="sql">

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></source>


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

   <source lang="sql">

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)</source>


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:



   <source lang="sql">

DATE_FORMAT(date, sequence)</source>


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.



   <source lang="sql">

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></source>


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

   <source lang="sql">

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></source>


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

   <source lang="sql">

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></source>


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

   <source lang="sql">

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></source>


%f: Microseconds (000000..999999)

   <source lang="sql">

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></source>


%H: Hour (00..23)

   <source lang="sql">

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)</source>


%h: Hour (01..12)

   <source lang="sql">

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)</source>


%I: Hour (01..12)

   <source lang="sql">

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></source>


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

   <source lang="sql">

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)</source>


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

   <source lang="sql">

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)</source>


%k: Hour (0..23)

   <source lang="sql">

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)</source>


%l: Hour (1..12)

   <source lang="sql">

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)</source>


%M: Month name (January..December)

   <source lang="sql">

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)</source>


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

   <source lang="sql">

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)</source>


%p: AM or PM

   <source lang="sql">

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)</source>


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

   <source lang="sql">

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)</source>


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

   <source lang="sql">

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></source>


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

   <source lang="sql">

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></source>


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

   <source lang="sql">

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></source>


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

   <source lang="sql">

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></source>


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

   <source lang="sql">

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></source>


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

   <source lang="sql">

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


%S: Seconds (00..59)

   <source lang="sql">

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)</source>


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

   <source lang="sql">

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)</source>


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

   <source lang="sql">

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></source>


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

   <source lang="sql">

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)</source>


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

   <source lang="sql">

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)</source>


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

   <source lang="sql">

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)</source>


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

   <source lang="sql">

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)</source>


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

   <source lang="sql">

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)</source>


%W: Weekday name (Sunday..Saturday)

   <source lang="sql">

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)</source>


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

   <source lang="sql">

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)</source>


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

   <source lang="sql">

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)</source>


%Y: Year, numeric, four digits

   <source lang="sql">

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)</source>


%y: Year, numeric (two digits)

   <source lang="sql">

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)</source>

Источник — «http://sqle.ru/index.php?title=MySQL_Tutorial/Date_Time_Functions/DATE_FORMAT&oldid=475»