SQL 10 函数 3 日期时间函数 - 7 取得日期的指定部分

提取日期的特定部分是非常必要的，比如检索本年度每个月16日的销售量、检索访问客户集中的时间段，这些需要对日期的特定部分进行提取。主流数据库对这个功能都提供了很好的支持，下面分别进行介绍。

1. MySQL

MySQL中提供了一个DATE_FORMAT()函数，用来将日期按照特定格式进行格式化，这个函数的参数格式如下：

DATE_FORMAT(date, format)

其中参数date为待计算的日期值，而参数format为格式化字符串，格式化字符串中可以采用如下的占位符：

占位符	说明
%a	缩写的星期几 (Sun…Sat)
%b	缩写的月份名（ Jan …… Dec ）
%c	数字形式的月份（ 0 …… 12 ）
%D	当月的第几天带英文后缀（ 0th ,1st ,2nd ,3rd ,… ）
%d	当前的第几天，两位数字形式，不足梁文则补零（ 00 …… 31 ）
%e	当月的第几天，数字形式（ 0 …… 31 ）
%f	毫秒
%H	24 小时制的小时（ 00 …… 23 ）
%h	12 小时制的小时（ 00 …… 12 ）
%I	12 小时制的小时（ 01 …… 12 ）
%i	数字形式的分钟（ 00 …… 59 ）
%j	日期在当年中的天数
%k	24 进制的小时（ 0 …… 23 ）
%l	12 进制的小时（ 1 …… 12 ）
%M	月份名（ January …… December ）
%m	两位数字表示的月份（ 00 …… 12 ）
%p	上午还是下午（ AM…PM ）
%r	12 小时制时间，比如 08:09:29
%S	秒数（ 00…59 ）
%s	秒数（ 00 …… 59 ）
%T	时间， 24 小时制，格式为 hh:mm:ss
%U	所属周是当年的第几周，周日当作第一天（ 00 …… 53 ）
%u	所属周是当年的第几周，周一当作第一天 （ 00 …… 53 ）
%V	所属周是当年的第几周，周日当作第一天 （ 01 …… 53 ）
%v	所属周是当年的第几周，周日当作第一天 （ 01 …… 53 ）
%W	星期几 (Sunday …… Saturday)
%w	星期几（ 0=Sunday …… 6=Sunday ）
%X	本周所属年，周日当作第一天
%x	本周所属年，周一当作第一天
%Y	年份数， 4 位数字
%y	年分数， 2 位数字

组合使用这些占位符就可以实现非常复杂的字符串格式化逻辑，比如下面的SQL语句将出生日期FBirthday和注册日期FRegday分别按照两种格式进行格式化：

SELECT

FBirthday,

DATE_FORMAT(FBirthday, '%y-%M %D %W') AS BD,

FRegday,

DATE_FORMAT(FRegday, '%Y年%m月%e日') AS RD

FROM T_Person

 

很明显，如果只适用单独的占位符就可以实现提取日期特定部分的功能了，比如DATE_FORMAT(date, %Y)可以用来提取日期的年份的部分，DATE_FORMAT(date, '%H')可以用来提取日期的小时部分，DATE_FORMAT(date,'%M')可以用来提取日期的月份名成。下面的SQL用于提取每个人的出生年份，出生时是当年的第几天和出生时是当年的第几周：

SELECT

FBirthday,

DATE_FORMAT(FBirthday,'%Y') as y,

DATE_FORMAT(FBirthday, '%j') as d,

DATE_FORMAT(FBirthday, '%U') as u

FROM T_Person

 

2. Oracle

在5.3.5的标题3中我们介绍了在Oracle中使用TO_CHAR()函数格式化日期的方法，使用它就可以提取日期的任意部分，比如下面的SQL用于提取每个人的出生年份，出生时尚当年的第几天和出生是谁当年的第几周：

SELECT

FBirthday,

TO_CHAR(FBirthday, 'YYYY') AS Y,

TO_CHAR(FBirthday, 'DDD') AS D,

TO_CHAR(FBirthday, 'WW') AS u,

FROM T_Person