How can we utilize the sql date_format function?

2 years ago
William Carter
1 minute

The DATE_FORMAT function in SQL is used for formatting dates. Its syntax is as follows:

DATE_FORMAT(date, format)

In this, date is the date or datetime field to be formatted, and format is the string representing the date format. Common date format strings are as follows:

  1. %Y: A four-digit year
  2. %y: Two-digit year
  3. %m: a two-digit month
  4. %c: Month
  5. %d: Two-digit date.
  6. %e: date
  7. %H: Hours in 24-hour format
  8. The hour number in a 12-hour clock.
  9. %i: number of minutes
  10. %s: seconds
  11. %p: AM or PM
  12. %W: Day of the week
  13. %M: Name of the month
  14. Abbreviation of month names
  15. %a: Abbreviation of the weekday name

Here are some examples:

SELECT DATE_FORMAT('2022-01-01', '%Y-%m-%d'); -- 输出 2022-01-01
SELECT DATE_FORMAT('2022-01-01', '%d/%m/%Y'); -- 输出 01/01/2022
SELECT DATE_FORMAT('2022-01-01 12:34:56', '%Y-%m-%d %H:%i:%s'); -- 输出 2022-01-01 12:34:56
SELECT DATE_FORMAT('2022-01-01 12:34:56', '%W, %M %e, %Y'); -- 输出 Saturday, January 1, 2022

By adjusting the value of the format parameter, different date formats can be obtained. The specific date format string can be adjusted as needed.

bannerAds