MariaDB DATE_FORMAT() 函数按照格式掩码指定的方式格式化日期或日期时间值。
语法
DATE_FORMAT(datetime, format_mask) 参数
| datetime | Required. Specify the date or datetime value to format. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| format_mask | Required. Specify the format to apply to the datetime. The following is a list of options for this parameter. It can be used in many combinations.
|
注意:在MariaDB中，日和月范围从00开始。它允许日期存储不完整。例如:"2019-00-00"。
返回值
返回由格式掩码指定的格式化日期或日期时间值。
示例 1:
下面的示例显示了DATE_FORMAT()函数的用法。
SELECT DATE_FORMAT('2018-08-18', '%Y');
Result: '2018'
SELECT DATE_FORMAT('2018-08-18', '%M %d, %Y');
Result: 'August 18, 2018'
SELECT DATE_FORMAT('2018-08-18', '%M %e %Y');
Result: 'August 18 2018'
SELECT DATE_FORMAT('2018-08-18', '%W, %M %e, %Y');
Result: 'Saturday, August 18, 2018'
SELECT DATE_FORMAT('2018-08-18', '%W');
Result: 'Saturday' 示例2:
考虑一个名为的数据库表具有以下记录的员工:
| Value | Description |
|---|---|
| %a | Weekday name abbreviated (Sun to Sat) |
| %b | Month name abbreviated (Jan to Dec) |
| %c | Month as a numeric value (0 to 12) |
| %D | Day of the month as a numeric value, followed by suffix (1st, 2nd, 3rd, ...) |
| %d | Day of the month as a numeric value (01 to 31) |
| %e | Day of the month as a numeric value (0 to 31) |
| %f | Microseconds (000000 to 999999) |
| %H | Hour (00 to 23) |
| %h | Hour (00 to 12) |
| %I | Hour (00 to 12) |
| %i | Minutes (00 to 59) |
| %j | Day of the year (001 to 366) |
| %k | Hour (00 to 23) |
| %l | Hour (1 to 12) |
| %M | Month name in full (January to December) |
| %m | Month name as a numeric value (00 to 12) |
| %p | AM or PM |
| %r | Time in 12 hour AM or PM format (hh:mm:ss AM/PM) |
| %S | Seconds (00 to 59) |
| %s | Seconds (00 to 59) |
| %T | Time in 24 hour format (hh:mm:ss) |
| %U | Week where Sunday is the first day of the week (00 to 53) |
| %u | Week where Monday is the first day of the week (00 to 53) |
| %V | Week where Sunday is the first day of the week (01 to 53). Used with %X |
| %v | Week where Monday is the first day of the week (01 to 53). Used with %X |
| %W | Weekday name in full (Sunday to Saturday) |
| %w | Day of the week where Sunday=0 and Saturday=6 |
| %X | Year for the week where Sunday is the first day of the week. Used with %V |
| %x | Year for the week where Monday is the first day of the week. Used with %v |
| %Y | Year as a numeric, 4-digit value |
| %y | Year as a numeric, 2-digit value |
| %% | A literal % character |
在下面的查询中，DATE_FORMAT()函数用于格式化Date_of_Joining列的日期值:
SELECT *,
DATE_FORMAT(Date_of_Joining, '%M %d, %Y') AS DATE_FORMAT_Value
FROM Employee; 这将产生类似于以下内容的结果:
| EmpID | Name | City | Age | Date_of_Joining |
|---|---|---|---|---|
| 1 | John | London | 25 | 2018-05-25 |
| 2 | Marry | New York | 24 | 2018-10-15 |
| 3 | Jo | Paris | 27 | 2019-06-09 |
| 4 | Kim | Amsterdam | 30 | 2019-09-21 |
| 5 | Ramesh | New Delhi | 28 | 2019-10-25 |