T-SQL日期和时间函数

fireWang

发布于 2020-02-26 10:45:47

1.9K0

发布于 2020-02-26 10:45:47

文章被收录于专栏：零维领域零维领域零维领域

本文目录：

日期和时间数据类型
获取系统日期和时间值函数
获取日期和时间部分值函数
获取日期和时间差函数
修改日期和时间值函数
验证日期和时间值函数
日期和时间转换

3.4.8 日期和时间函数

日期和时间数据类型及函数的信息和示例

3.4.8.1 日期和时间数据类型

下表列出了 Transact-SQL 的日期和时间数据类型。

-- 示例各个日期、时间数据类型
SELECT 
CAST('2020-02-02 12:13:14.1234567' AS time(7)) AS 'time', 
CAST('2020-02-02 12:13:14.1234567' AS date) AS 'date', 
CAST('2020-02-02 12:13:14.123' AS smalldatetime) AS 'smalldatetime',
CAST('2020-02-02 12:13:14.123' AS datetime) AS 'datetime', 
CAST('2020-02-02 12:13:14.1234567' AS datetime2(7)) AS 'datetime2',
CAST('2020-02-02 12:13:14.1234567' AS datetimeoffset(7)) AS 'datetimeoffset';

date_and_time

3.4.8.2 系统日期和时间值

所有系统日期和时间值均得自运行 SQL Server 实例的计算机的操作系统。

精度较高 的系统日期和时间函数

SQL Server 2008 R2 使用 GetSystemTimeAsFileTime() Windows API 来获取日期和时间值。精确程度取决于运行 SQL Server 实例的计算机硬件和 Windows 版本。此 API 的精度固定为 100 纳秒。可通过使用 GetSystemTimeAdjustment() Windows API 来确定该精确度。

SELECT 
  SYSDATETIME() AS 'SYSDATETIME',
  SYSDATETIMEOFFSET() AS 'SYSDATETIMEOFFSET',
  SYSUTCDATETIME() AS 'SYSUTCDATETIME';

sysdatetime

精度较低 的系统日期和时间函数

SELECT 
  CURRENT_TIMESTAMP AS 'CURRENT_TIMESTAMP',
  GETDATE() AS 'DATE',
  GETUTCDATE() AS 'UTCDATE';

sysdate

3.4.8.3 日期和时间部分值

DATENAME() 和DATEPART() 的 datepart参数完全一样，并且datepart的全写和缩写完全等价，DATENAME()和DATEPART()仅在部分datepart下输出值的不同（当然两种输出值的数据类型是完全不一样的）

SELECT '2020-02-02 12:13:14.1234567';
SELECT 
    DATENAME(year, '2020-02-02 12:13:14.1234567') as 'year',
    DATENAME(quarter, '2020-02-02 12:13:14.1234567') as 'quarter',
    DATENAME(month, '2020-02-02 12:13:14.1234567') as 'month',
    DATENAME(dayofyear, '2020-02-02 12:13:14.1234567') as 'dayofyear',
    DATENAME(day, '2020-02-02 12:13:14.1234567') as 'day',
    DATENAME(week, '2020-02-02 12:13:14.1234567') as 'week',
    DATENAME(weekday, '2020-02-02 12:13:14.1234567') as 'weekday',
    DATENAME(hour, '2020-02-02 12:13:14.1234567') as 'hour',
    DATENAME(minute, '2020-02-02 12:13:14.1234567') as 'minute',
    DATENAME(second, '2020-02-02 12:13:14.1234567') as 'second',
    DATENAME(millisecond, '2020-02-02 12:13:14.1234567') as 'millisecond',
    DATENAME(microsecond, '2020-02-02 12:13:14.1234567') as 'mocrosecond',
    DATENAME(nanosecond, '2020-02-02 12:13:14.1234567') as 'nanosecond',
    --返回偏移量
    DATENAME(TZoffset, '2020-02-02 12:13:14.1234567 +8:00') as 'TZoffset',
    DATENAME(ISO_WEEK, '2020-02-02 12:13:14.1234567') as 'ISO_WEEK';
SELECT 
    DATEPART(yy, '2020-02-02 12:13:14.1234567') as 'year',
    DATEPART(q, '2020-02-02 12:13:14.1234567') as 'quarter',
    DATEPART(mm, '2020-02-02 12:13:14.1234567') as 'month',
    DATEPART(dy, '2020-02-02 12:13:14.1234567') as 'dayofyear',
    DATEPART(dd, '2020-02-02 12:13:14.1234567') as 'day',
    DATEPART(wk, '2020-02-02 12:13:14.1234567') as 'week',
    DATEPART(dw, '2020-02-02 12:13:14.1234567') as 'weekday',
    DATEPART(hh, '2020-02-02 12:13:14.1234567') as 'hour',
    DATEPART(mi, '2020-02-02 12:13:14.1234567') as 'minute',
    DATEPART(ss, '2020-02-02 12:13:14.1234567') as 'second',
    DATEPART(ms, '2020-02-02 12:13:14.1234567') as 'millisecond',
    DATEPART(mcs, '2020-02-02 12:13:14.1234567') as 'mocrosecond',
    DATEPART(ns, '2020-02-02 12:13:14.1234567') as 'nanosecond',
    --返回偏移的分钟数
    DATEPART(tz, '2020-02-02 12:13:14.1234567 +8:00') as 'TZoffset',
    DATEPART(isowk, '2020-02-02 12:13:14.1234567') as 'ISO_WEEK';

part_of_date_and_time

对于DATEPART()，当 datepart 为 week (wk, ww) 或 weekday (dw) 时，返回值取决于使用 SET DATEFIRST 设置的值。

任何年份的 1 月 1 日都用来定义 weekdatepart 的起始数字，例如：DATEPART (wk, 'Jan 1, xxxx') = 1，其中 xxxx 为任意年份。

下表列出了针对每个不同的 SET DATEFIRST 参数，“2007-04-21”的 week 和 weekdaydatepart 返回值。1 月 1 日在 2007 年是星期日。4 月 21 日在 2007 年是星期六。SET DATEFIRST 7, Sunday 是美国英语的默认值。

此时相当于星期天被指示为一周的第一天，因此星期六为最后一天，返回值为7。

对于DATEPART() 的 ISO_WEEK ，遵循ISO 8601，包括 ISO 周-日期系统，即周的编号系统。

每周都与该周内星期四所在的年份关联。例如，2004 年的第一周 (2004W01) 是指从 2003 年 12 月 29 日（星期一）到 2004 年 1 月 4 日（星期日）。一年中最大的周编号可能是 52 或 53。此样式的编号通常用于欧洲国家/地区，其他地方很少使用。

不同的国家/地区的编号系统可能不符合 ISO 标准。现在至少可能存在六种编号系统，如下表所示：

很容易发现，DAY(), MONTH(), YEAR() 的实现在 DATEPART() 中都已经实现了。

3.4.8.4 日期和时间差

datepart参数与 DATEPART() 中除 TZoffset 和 ISO_WEEK 外完全一致，可以认为 DATEDIFF(datepart , startdate , enddate) 就是 DATEPART(datepart , startdate) 与 DATEPART(datepart , enddate) 的差值

3.4.8.5 修改日期和时间值

DATEADD() 的datepart参数与 DATEPART() 中除 TZoffset 和 ISO_WEEK 外完全一致。特别的是，参数中的 number只能是整数，即int值，如果是浮点数，那么会自动被转换为 int值。

SWITCHOFFSET (DATETIMEOFFSET , time_zone) 中 time_zone 是一个格式为 [+|-]TZH:TZM 的字符串，或是一个表示时区偏移量的带符号的整数（分钟数）。time_zone的范围为 +14 到 -13 ，或者是同样长度的分钟数。

SELECT SWITCHOFFSET('2020-02-02 12:13:14.1234567','+08:00');
SELECT SWITCHOFFSET('2020-02-02 12:13:14.1234567','-08:00');
SELECT SWITCHOFFSET('2020-02-02 12:13:14.1234567',60);

switchtimezone

TODATETIMEOFFSET(expression , time_zone) 和 SWITCHOFFSET(DATETIMEOFFSET , time_zone) 用法类似，只不过需要 expression参数为返回值为datetime2数据类型的表达式。

3.4.8.6 验证日期和时间值

expression: 字符串或者可以转换为字符串表达式。

SELECT ISDATE('12:13:14.1234567') AS 'time'; 
SELECT ISDATE('2020-02-02') AS 'date';
SELECT ISDATE('2020-02-02 12:13:14') AS 'smalldatetime';
SELECT ISDATE('2020-02-02 12:13:14.123') AS 'datetime';
SELECT ISDATE('2020-02-02 12:13:14.1234567') AS 'datetime2';
SELECT ISDATE('2020-02-02 12:13:14.1234567 +8:00') AS 'datetimeoffset';

isdate

3.4.8.7 日期和时间相关主题

这部分主要关注利用cast()和convert() 转换日期和时间数据类型。这是日常最常用的。

-- CAST()语法，数据类型之间相互转换:
CAST ( expression AS data_type [ ( length ) ] )
-- CONVERT()语法， 将指定style的数据类型值转化为另一数据类型:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

如果 expression 为 date 或 time 数据类型，则 style 可以为下表中显示的值之一。其他值作为 0 进行处理。SQL Server 使用科威特算法来支持阿拉伯样式（回历）的日期格式。

--style指定的是源数据的格式
--新数据的格式由数据类型来决定
SELECT CONVERT(date, 'Jan 22 2020 12:13:14', 100);
SELECT CONVERT(datetime, '01/22/2020', 101);
SELECT CONVERT(datetime2, '2020.01.22', 102);
SELECT CONVERT(smalldatetime, '22/01/2020', 103);
SELECT CONVERT(date, '22/01/2020', 104);
SELECT CONVERT(date, '22-01-2020', 105);
SELECT CONVERT(date, '22 Jan 2020', 106);
SELECT CONVERT(date, 'Jan 22,2020', 107);
SELECT CONVERT(time(5), '12:13:14', 108);
SELECT CONVERT(smalldatetime, 'Jan 22 2020 12:13:14.123', 109);
SELECT CONVERT(date, '01-22-2020', 110);
SELECT CONVERT(date, '2020/01/22', 111);
SELECT CONVERT(date, '20200122', 112);
SELECT CONVERT(date, '22 Jan 2020 12:13:14.123', 113);
SELECT CONVERT(time(7), '12:13:14.123', 114);
SELECT CONVERT(datetime, '2020-01-22 12:13:14', 120); 
SELECT CONVERT(datetime, '2020-01-22 12:13:14.123', 121);
SELECT CONVERT(smalldatetime, '2020-01-22T12:13:14.123', 126);
SELECT CONVERT(smalldatetime, '2020-01-22T12:13:14.123', 127);