当前位置：首页 > 资讯 > 技术文档

Mysql 日期时间 DATE_FORMAT(date,format)

时间：2020-10-01 10:49 编辑：来源：阅读：
扫一扫，手机访问

摘要：Mysql 日期时间 DATE_FORMAT(date,format)

本文转自：http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html#function_date-format [*] [code]DATE_FORMAT([i]date[/code][/i],[i][code]format[/code][/i]) Formats the [i][code]date[/code][/i] value according to the [i][code]format[/code][/i] string. The following specifiers may be used in the [i][code]format[/code][/i] string. As of MySQL 3.23, the “[code]%[/code]” character is required before format specifier characters. In earlier versions of MySQL, “[code]%[/code]” was optional.

Specifier	Description
[code]%a[/code]	Abbreviated weekday name ([code]Sun[/code]..[code]Sat[/code])
[code]%b[/code]	Abbreviated month name ([code]Jan[/code]..[code]Dec[/code])
[code]%c[/code]	Month, numeric ([code]0[/code]..[code]12[/code])
[code]%D[/code]	Day of the month with English suffix ([code]0th[/code], [code]1st[/code], [code]2nd[/code], [code]3rd[/code], …)
[code]%d[/code]	Day of the month, numeric ([code]00[/code]..[code]31[/code])
[code]%e[/code]	Day of the month, numeric ([code]0[/code]..[code]31[/code])
[code]%f[/code]	Microseconds ([code]000000[/code]..[code]999999[/code])
[code]%H[/code]	Hour ([code]00[/code]..[code]23[/code])
[code]%h[/code]	Hour ([code]01[/code]..[code]12[/code])
[code]%I[/code]	Hour ([code]01[/code]..[code]12[/code])
[code]%i[/code]	Minutes, numeric ([code]00[/code]..[code]59[/code])
[code]%j[/code]	Day of year ([code]001[/code]..[code]366[/code])
[code]%k[/code]	Hour ([code]0[/code]..[code]23[/code])
[code]%l[/code]	Hour ([code]1[/code]..[code]12[/code])
[code]%M[/code]	Month name ([code]January[/code]..[code]December[/code])
[code]%m[/code]	Month, numeric ([code]00[/code]..[code]12[/code])
[code]%p[/code]	[code]AM[/code] or [code]PM[/code]
[code]%r[/code]	Time, 12-hour ([code]hh:mm:ss[/code] followed by [code]AM[/code] or [code]PM[/code])
[code]%S[/code]	Seconds ([code]00[/code]..[code]59[/code])
[code]%s[/code]	Seconds ([code]00[/code]..[code]59[/code])
[code]%T[/code]	Time, 24-hour ([code]hh:mm:ss[/code])
[code]%U[/code]	Week ([code]00[/code]..[code]53[/code]), where Sunday is the first day of the week
[code]%u[/code]	Week ([code]00[/code]..[code]53[/code]), where Monday is the first day of the week
[code]%V[/code]	Week ([code]01[/code]..[code]53[/code]), where Sunday is the first day of the week; used with [code]%X[/code]
[code]%v[/code]	Week ([code]01[/code]..[code]53[/code]), where Monday is the first day of the week; used with [code]%x[/code]
[code]%W[/code]	Weekday name ([code]Sunday[/code]..[code]Saturday[/code])
[code]%w[/code]	Day of the week ([code]0[/code]=Sunday..[code]6[/code]=Saturday)
[code]%X[/code]	Year for the week where Sunday is the first day of the week, numeric, four digits; used with [code]%V[/code]
[code]%x[/code]	Year for the week, where Monday is the first day of the week, numeric, four digits; used with [code]%v[/code]
[code]%Y[/code]	Year, numeric, four digits
[code]%y[/code]	Year, numeric (two digits)
[code]%%[/code]	A literal “[code]%[/code]” character
[code]%[i]`x[/code][/i]`	[i][code]x[/code][/i], for any “[i][code]x[/code][/i]” not listed above

The [code]%v[/code], [code]%V[/code], [code]%x[/code], and [code]%X[/code] format specifiers are available as of MySQL 3.23.8. [code]%f[/code] is available as of MySQL 4.1.1. Ranges for the month and day specifiers begin with zero due to the fact that MySQL permits the storing of incomplete dates such as [code]'2014-00-00'[/code] (as of MySQL 3.23). As of MySQL 4.1.21, the language used for day and month names and abbreviations is controlled by the value of the [code]lc_time_names[/code] system variable (Section 9.8, “MySQL Server Locale Support”). As of MySQL 4.1.23, [code]DATE_FORMAT()[/code] returns a string with a character set and collation given by [code]character_set_connection[/code] and [code]collation_connection[/code] so that it can return month and weekday names containing non-ASCII characters. Before 4.1.23, the return value is a binary string.

mysql> [code]SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y');[/code]
    -> 'Sunday October 2009'
mysql> [code]SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s');[/code]
    -> '22:23:00'
mysql> [code]SELECT DATE_FORMAT('1900-10-04 22:23:00',[/code]
  -> [code]        '%D %y %a %d %m %b %j');[/code]
    -> '4th 00 Thu 04 10 Oct 277'
mysql> [code]SELECT DATE_FORMAT('1997-10-04 22:23:00',[/code]
  -> [code]        '%H %k %I %r %T %S %w');[/code]
    -> '22 22 10 10:23:00 PM 22:23:00 00 6'
mysql> [code]SELECT DATE_FORMAT('1999-01-01', '%X %V');[/code]
    -> '1998 52'
mysql> [code]SELECT DATE_FORMAT('2006-06-00', '%d');[/code]
    -> '00'

[/*]

全部评论(0)

上一篇：VirtualBox中CentOS7安装教程
下一篇：MySQL数据库中把int转化varchar引发的慢查询

资讯排行榜
更多>>