date_format

Description

Syntax

VARCHAR DATE_FORMAT (DATETIME DATE, VARCHAR Format)

Convert the date type to a string according to the format type. Convert the date type to a string according to the format type. Currently, it supports a maximum of 128 bytes for the string. If the return value length exceeds 128 bytes, then it returns NULL.

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, only available on 12-hours system

% R | Time, 12-hour (hh:mm:ss), could be with or without AM/PM marking

% 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. +------------------------------------------------+
  7. mysql> select date_format('2007-10-04 22:23:00', '%H:%i:%s');
  8. +------------------------------------------------+
  9. | date_format('2007-10-04 22:23:00', '%H:%i:%s') |
  10. +------------------------------------------------+
  11. | 22:23:00 |
  12. +------------------------------------------------+
  13. mysql> select date_format('1900-10-04 22:23:00', '%D %y %a %d %m %b %j');
  14. +------------------------------------------------------------+
  15. | date_format('1900-10-04 22:23:00', '%D %y %a %d %m %b %j') |
  16. +------------------------------------------------------------+
  17. | 4th 00 Thu 04 10 Oct 277 |
  18. +------------------------------------------------------------+
  19. mysql> select date_format('1997-10-04 22:23:00', '%H %k %I %r %T %S %w');
  20. +------------------------------------------------------------+
  21. | date_format('1997-10-04 22:23:00', '%H %k %I %r %T %S %w') |
  22. +------------------------------------------------------------+
  23. | 22 22 10 10:23:00 PM 22:23:00 00 6 |
  24. +------------------------------------------------------------+
  25. mysql> select date_format('1999-01-01 00:00:00', '%X %V');
  26. +---------------------------------------------+
  27. | date_format('1999-01-01 00:00:00', '%X %V') |
  28. +---------------------------------------------+
  29. | 1998 52 |
  30. +---------------------------------------------+
  31. mysql> select date_format('2006-06-01', '%d');
  32. +------------------------------------------+
  33. | date_format('2006-06-01 00:00:00', '%d') |
  34. +------------------------------------------+
  35. | 01 |
  36. +------------------------------------------+
  37. mysql> select date_format('2006-06-01', '%%%d');
  38. +--------------------------------------------+
  39. | date_format('2006-06-01 00:00:00', '%%%d') |
  40. +--------------------------------------------+
  41. | %01 |
  42. +--------------------------------------------+

keywords

  1. DATE_FORMAT,DATE,FORMAT