date_format

Description

Syntax

VARCHAR DATE' FORMAT (DATETIME DATE, VARCHAR Format)

Convert the date type to a bit string according to the format type. Currently supports a string with a maximum 128 bytes and returns NULL if the length of the return value exceeds 128

The date parameter is the valid date. Format specifies the date/time output format.

The formats available are:

% a | Abbreviation for Sunday Name

% B | Abbreviated Monthly Name

% C | Month, numerical value

% D | Sky in the Moon with English Prefix

% d | Monthly day, numerical value (00-31)

% e | Monthly day, numerical value (0-31)

% f | microseconds

% H | Hours (00-23)

% h | hour (01-12)

% I | Hours (01-12)

% I | min, numerical value (00-59)

% J | Days of Year (001-366)

% k | hours (0-23)

% L | Hours (1-12)

% M | Moon Name

% m | month, numerical value (00-12)

%p | AM or PM

% R | Time, 12 - hour (hh: mm: SS AM or PM)

% S | seconds (00-59)

% s | seconds (00-59)

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

% U | Week (00-53) Sunday is the first day of the week

% U | Week (00 - 53) Monday is the first day of the week

% V | Week (01-53) Sunday is the first day of the week, and% X is used.

% v | Week (01 - 53) Monday is the first day of the week, and% x is used

% W | Sunday

% w | Weekly day (0 = Sunday, 6 = Saturday)

% X | Year, where Sunday is the first day of the week, 4 places, and% V use

% X | year, of which Monday is the first day of the week, 4 places, and% V

% Y | Year, 4

% Y | Year, 2

%% | Represent %

Also support 3 formats:

yyyyMMdd

yyyy-MM-dd

yyyy-MM-dd HH:mm:ss

example

  1. mysql> select date_format('2009-10-04 22:23:00', '%W %M %Y');
  2. +------------------------------------------------+
  3. | date_format('2009-10-04 22:23:00', '%W %M %Y') |
  4. +------------------------------------------------+
  5. | Sunday October 2009                            |
  6. +------------------------------------------------+
  8. mysql> select date_format('2007-10-04 22:23:00', '%H:%i:%s');
  9. +------------------------------------------------+
  10. | date_format('2007-10-04 22:23:00', '%H:%i:%s') |
  11. +------------------------------------------------+
  12. | 22:23:00                                       |
  13. +------------------------------------------------+
  15. mysql> select date_format('1900-10-04 22:23:00', '%D %y %a %d %m %b %j');
  16. +------------------------------------------------------------+
  17. | date_format('1900-10-04 22:23:00', '%D %y %a %d %m %b %j') |
  18. +------------------------------------------------------------+
  19. | 4th 00 Thu 04 10 Oct 277                                   |
  20. +------------------------------------------------------------+
  22. mysql> select date_format('1997-10-04 22:23:00', '%H %k %I %r %T %S %w');
  23. +------------------------------------------------------------+
  24. | date_format('1997-10-04 22:23:00', '%H %k %I %r %T %S %w') |
  25. +------------------------------------------------------------+
  26. | 22 22 10 10:23:00 PM 22:23:00 00 6                         |
  27. +------------------------------------------------------------+
  29. mysql> select date_format('1999-01-01 00:00:00', '%X %V'); 
  30. +---------------------------------------------+
  31. | date_format('1999-01-01 00:00:00', '%X %V') |
  32. +---------------------------------------------+
  33. | 1998 52                                     |
  34. +---------------------------------------------+
  36. mysql> select date_format('2006-06-01', '%d');
  37. +------------------------------------------+
  38. | date_format('2006-06-01 00:00:00', '%d') |
  39. +------------------------------------------+
  40. | 01                                       |
  41. +------------------------------------------+
  43. mysql> select date_format('2006-06-01', '%%%d');
  44. +--------------------------------------------+
  45. | date_format('2006-06-01 00:00:00', '%%%d') |
  46. +--------------------------------------------+
  47. | %01                                        |
  48. +--------------------------------------------+

keywords

  1. DATE_FORMAT,DATE,FORMAT