SQL Server日期时间函数

作者:余俊峰
本作品采用署名-相同方式共享许可协议授权。

1. 数据类型

类型:datetime
格式:YYYY-MM-DD hh:mm:ss[.nnn]
范围:1753-01-01到9999-12-31
精确度:0.00333秒
存储大小(以字节为单位):8

2. 测试环境
2.1. 环境

SQL Server 2012,数据库yuTest。

2.2. 创建表tblDateTime

2.3. 插入测试数据

3. 函数用法示例
3.1. 用来获取系统日期和时间值的函数
3.1.1. GETDATE()

返回包含计算机的日期和时间的datetime值。

返回类型:datetime

例如执行:

结果集:

3.1.2. GETUTCDATE()

返回包含计算机的UTC日期和时间的datetime值。

返回类型:datetime

例如执行:

结果集:

3.2. 用来获取日期和时间部分的函数
3.2.1. DATENAME(datepart, date)

返回表示指定日期的指定datepart的字符串。

参数:
datepart – 是指定应返回的日期部分的参数。下表列出了 Microsoft SQL Server 识别的日期部分和缩写。
date – 是一个可以解析为 time、date、smalldatetime、datetime、datetime2 或 datetimeoffset 值的表达式。 date 可以是表达式、列表达式、用户定义的变量或字符串文字。为避免不确定性,请使用四位数年份。

返回类型:nvarchar

返回值:
每个datepart及其缩写都返回相同的值。
返回值因使用SET LANGUAGE和登录的配置默认语言服务器配置选项设置的语言环境的不同而异。如果date是某些格式的字符串文字,则返回值由SET DATEFORMAT而定。当日期为日期或时间数据类型的列表达式时,SET DATEFORMAT不影响返回值。

例如执行:

结果集:

3.2.2. DATEPART(datepart, date)

返回表示指定date的指定datepart的整数。

参数:
datepart – 参见DATENAME节。
date – 是一个可以解析为time、date、smalldatetime、datetime、datetime2或datetimeoffset值的表达式。date可以是表达式、列表达式、用户定义的变量或字符串文字。为避免不确定性,请使用四位数年份。

返回类型:int

返回值:
每个datepart及其缩写都返回相同的值。
返回值因使用SET LANGUAGE和登录的配置默认语言服务器配置选项设置的语言环境的不同而异。如果date为某些格式的字符串文字,则返回值由使用SET DATEFORMAT指定的格式来确定。当日期为日期或时间数据类型的列表达式时,SET DATEFORMAT不影响返回值。

例如执行:

结果集:

3.2.3. DAY(date)

返回表示指定date的“日”部分的整数。

参数:
date – 是一个可以解析为 time、date、smalldatetime、datetime、datetime2 或 datetimeoffset 值的表达式。 date 可以是表达式、列表达式、用户定义的变量或字符串文字。为避免不确定性,请使用四位数年份。

返回类型:int

返回值:
DAY与DATEPART(day、date)返回相同的值。
如果date只包含时间部分,则返回值为1,即基准日。

例如执行:

结果集:

3.2.4. MONTH(date)

返回表示指定date的“月”部分的整数。

参数:
date – 是一个可以解析为 time、date、smalldatetime、datetime、datetime2 或 datetimeoffset 值的表达式。 date 可以是表达式、列表达式、用户定义的变量或字符串文字。为避免不确定性,请使用四位数年份。

返回类型:int

返回值:
MONTH返回的值与DATEPART(month, date)所返回的值相同。
如果date只包含时间部分,则返回值为1,即基准月。

例如执行:

结果集:

3.2.5. YEAR(date)

返回表示指定date的“年”部分的整数。

参数:
date – 是一个可以解析为 time、date、smalldatetime、datetime、datetime2 或 datetimeoffset 值的表达式。 date 可以是表达式、列表达式、用户定义的变量或字符串文字。为避免不确定性,请使用四位数年份。

返回类型:int

返回值:
YEAR与DATEPART(year, date)返回相同的值。
如果date仅包含一个时间部分,则返回值为1900,即基准年。

例如执行:

结果集:

3.3. 用来从部件中获取日期和时间值的函数
3.3.1. DATETIMEFROMPARTS(year, month, day, hour, minute, seconds, milliseconds)

为指定的日期和时间返回datetime值。

参数:
year – 用于指定年的整数表达式。
month – 用于指定月的整数表达式。
day – 用于指定日的整数表达式。
hour – 用于指定小时的整数表达式。
minute – 用于指定分钟的整数表达式。
seconds – 用于指定秒的整数表达式。
milliseconds – 用于指定毫秒的整数表达式。

返回类型:datetime

例如执行:

结果集:

3.3.2. DATETIMEOFFSETFROMPARTS(year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision)

对指定的日期和时间返回datetimeoffset值,具有指定的偏移量和精度。

参数:
year – 用于指定年的整数表达式。
month – 用于指定月的整数表达式。
day – 用于指定日的整数表达式。
hour – 用于指定小时的整数表达式。
minute – 用于指定分钟的整数表达式。
seconds – 用于指定秒的整数表达式。
fractions – 用于指定小数部分的整数表达式。
hour_offset – 整数表达式,用于指定时区偏移量的小时部分。
minute_offset – 整数表达式,用于指定时区偏移量的分钟部分。
precision – 整数文字,用于指定要返回的 datetimeoffset 值的精度。

返回类型:datetimeoffset(precision)

注释:
DATETIMEOFFSETFROMPARTS返回完全初始化的datetimeoffset数据类型。偏移量参数用来表示时区偏移量。如果忽略偏移量参数,则认为时区偏移量是00:00,也即没有时区偏移量。如果指定了偏移量参数,则必须存在这两个参数,且两者必须为正数或负数。如果指定了minute_offset,但没有hour_offset,将引发错误。如果其他参数无效,则引发错误。如果所需的参数为null,则返回null。但是,如果precision参数为Null,则会引发错误。
fractions参数取决于precision参数。例如,如果precision为7,则每个分数表示100毫微秒;如果precision为3,则每个分数表示1毫秒。如果precision的值为零,则fractions的值也必须为零;否则将引发错误。

例如执行:

fractions的值为500、precision的值为3,fractions的值表示500/1000秒。

结果集:

3.4. 用来获取日期和时间差的函数
3.4.1. DATEDIFF(datepart, startdate, enddate)

返回指定的startdate和enddate之间所跨的指定datepart边界的计数(带符号的整数)。

参数:
datepart – 参见DATENAME节。
startdate – 是一个可以解析为time、date、smalldatetime、datetime、datetime2或datetimeoffset值的表达式。date可以是表达式、列表达式、用户定义的变量或字符串文字。从enddate中减去startdate。为避免不确定性,请使用四位数年份。
enddate – 参见startdate。

返回类型:int

返回值:
每个datepart及其缩写都返回相同的值。
如果返回值超出int的范围(-2,147,483,648到+2,147,483,647),则会返回一个错误。对于millisecond,startdate与enddate之间的最大差值为24天20小时31分钟23.647秒。对于second,最大差值为68年。
如果为startdate和enddate都只指定了时间值,并且datepart不是时间datepart,则会返回0。
在计算返回值时不使用startdate或endate的时区偏移量部分。
如果只为某个日期数据类型的变量指定时间值,则所缺日期部分的值将设置为默认值:1900-01-01。如果只为某个时间或日期数据类型的变量指定日期值,则所缺时间部分的值将设置为默认值:00:00:00。如果startdate和enddate中有一个只含时间部分,另一个只含日期部分,则所缺时间和日期部分将设置为各自的默认值。
如果startdate和enddate属于不同的日期数据类型,并且其中一个的时间部分或秒小数部分精度比另一个高,则另一个的所缺部分将设置为0。

注释:
DATEDIFF可用在选择列表、WHERE、HAVING、GROUP BY和ORDER BY子句中。
DATEDIFF将字符串文字作为datetime2类型隐式转换。这就意味着,日期在作为字符串传递时,DATEDIFF不会支持YDM格式。必须先将字符串显式转换为datetime或smalldatetime类型,然后才能使用YDM格式。
指定SET DATEFIRST对DATEDIFF不起作用。DATEDIFF始终使用星期日作为每周的第一天,以确保函数是确定性的。

例如执行:

结果集:

3.5. 用来修改日期和时间值的函数
3.5.1. DATEADD(datepart, number, date)

将指定number时间间隔(有符号整数)与指定date的指定datepart相加后,返回该date。

参数:
datepart – 参见DATENAME节。
number – 一个表达式,它可以解析为与date的datepart相加的int。用户定义的变量是有效的。
如果指定一个带小数的值,则将小数截去且不进行舍入。
date – 是一个可以解析为 time、date、smalldatetime、datetime、datetime2 或 datetimeoffset 值的表达式。 date 可以是表达式、列表达式、用户定义的变量或字符串文字。为避免不确定性,请使用四位数年份。

返回类型:
返回数据类型为date参数的数据类型,字符串文字除外。
字符串文字的返回数据类型为datetime。如果字符串文字的秒数小数位数超过三位(.nnn)或包含时区偏移量部分,将引发错误。

注释:
DATEADD可用在SELECT <list>、WHERE、HAVING、GROUP BY和ORDER BY子句中。
不允许将date数据类型smalldatetime、date和datetime的microsecond或nanosecond的datepart相加。
毫秒的小数位数为3(.123);微秒的小数位数为6(.123456);纳秒的小数位数为9(.123456789)。time、datetime2和datetimeoffset数据类型的最大小数位数为7(.1234567)。如果datepart为nanosecond,则number必须为100才能使date的秒小数部分增加。介于1和49之间的number向下舍入为0,介于50和99之间的number向上舍入为100。

例如执行:

结果集:

通过将一个时间间隔与指定date的指定datepart相加,返回一个新的datetime值。

3.6. 用来验证日期和时间值的函数
3.6.1. ISDATE(expression)

确定expression是否为有效的日期或时间值。
如果expression是有效的date、time或datetime值,则返回1;否则,返回0。
如果expression为datetime2值,则ISDATE返回0。

参数:
expression – 字符串或者可以转换为字符串的表达式。表达式的长度不得超过4000个字符。不允许将日期和时间数据类型(datetime和 malldatetime除外)作为ISDATE的参数。

返回类型:int

注释:
只有与CONVERT函数一起使用,同时指定了CONVERT样式参数且样式不等于0、100、9或109时,ISDATE才是确定的。
ISDATE的返回值取决于SET DATEFORMAT、SET LANGUAGE和配置默认语言服务器配置选项设定的设置。

例如执行:

结果集:

3.7. 可用datetime数据类型的转换函数
3.7.1. 语法

CAST语法:CAST(expression AS data_type [(length)])
CONVERT语法:CONVERT(data_type [(length)], expression [, style])

3.7.2. 参数

expression – 任何有效的表达式。
data_type – 目标数据类型。这包括xml、bigint和sql_variant。不能使用别名数据类型。
length – 指定目标数据类型长度的可选整数。默认值为30。
style – 指定CONVERT函数如何转换expression的整数表达式。如果样式为NULL,则返回NULL。该范围是由data_type确定。

3.7.3. CONVERT的样式参数(仅列出部分值)

如果expression为日期或时间数据类型,则style可以为下表中显示的值之一:

标准:datetime的默认值
格式:0 或 100 = mon dd yyyy hh:miAM(或 PM)

标准:美国
格式:1 = mm/dd/yy  或 101 = mm/dd/yyyy

标准:ANSI
格式:2 = yy.mm.dd 或 102 = yyyy.mm.dd

标准:默认格式 + 毫秒
格式:9 或 109 = mon dd yyyy hh:mi:ss:mmmAM(或 PM)

标准:美国
格式:10 = mm-dd-yy 或 110 = mm-dd-yyyy

标准:日本
格式:11 = yy/mm/dd 或 111 = yyyy/mm/dd

标准:ISO
格式:12 = yymmdd 或 112 = yyyymmdd

标准:欧洲默认格式 + 毫秒
格式:13 或 113 = dd mon yyyy hh:mi:ss:mmm(24h)

标准:-
格式:14 或 114 = hh:mi:ss:mmm(24h)

标准:ODBC规范
格式:20 或 120 = yyyy-mm-dd hh:mi:ss(24h)

标准:ODBC规范(带毫秒)
格式:21 或 121 = yyyy-mm-dd hh:mi:ss.mmm(24h)

标准:ISO8601
格式:126 = yyyy-mm-ddThh:mi:ss.mmm(无空格)
注意:毫秒 (mmm) 的值为 0 时,不显示毫秒值。例如,值“2012-11-07T18:26:20.000”显示为“2012-11-07T18:26:20”。

3.7.4. 对日期时间数据使用CAST和CONVERT的示例

以下示例显示当前日期和时间,使用CAST将当前日期和时间更改为字符数据类型,然后使用CONVERT以ISO 8901格式显示日期和时间。

执行后结果集:

以下示例大致与上述示例相反。该示例将日期和时间显示为字符数据,使用CAST将字符数据更改为datetime数据类型,然后使用CONVERT将字符数据更改为datetime数据类型。

执行后结果集: