Mysql DATE FORMAT
From w3cyberlearnings
Contents |
MySQL DATE_FORMAT Function
This function formats date or timestamp.
Syntax DATE_FORMAT
- expre: date or datetime expression
- format:
- %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
DATE_FORMAT(expre, format);
Example 1
mysql> SELECT DATE_FORMAT('2012-05-22', '%W %d %M %Y');
+------------------------------------------+
| DATE_FORMAT('2012-05-22', '%W %d %M %Y') |
+------------------------------------------+
| Tuesday 22 May 2012 |
+------------------------------------------+
1 row in set (0.00 sec)
Output
mysql> SELECT DATE_FORMAT('2012-05-22 12:04:40', '%D %b %Y %a %d %m %j');
+------------------------------------------------------------+
| DATE_FORMAT('2012-05-22 12:04:40', '%D %b %Y %a %d %m %j') |
+------------------------------------------------------------+
| 22nd May 2012 Tue 22 05 143 |
+------------------------------------------------------------+
1 row in set (0.00 sec)
