Admin: 1 версия

2010-05-26T09:50:37Z

1 версия

Новая страница

== %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>

MySQL Tutorial/Date Time Functions/DATE FORMAT - История изменений

в 13:44, 26 мая 2010

Admin: 1 версия

← Предыдущая	Версия 13:44, 26 мая 2010
(нет различий)