专栏首页零维领域T-SQL日期和时间函数

T-SQL日期和时间函数

本文目录:

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

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);

本文项目地址:

https://github.com/firewang/sql50

(喜欢的话,Star一下)

阅读原文,或者访问该链接可以在线观看

https://sql50.readthedocs.io/zh_CN/latest/

参考网址:

  • https://docs.microsoft.com/zh-cn/previous-versions/sql/sql-server-2008-r2/ms175995(v=sql.105)

本文分享自微信公众号 - 零维领域(lingweilingyu),作者:fireWang

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2020-02-20

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 《机器学习》-- 第三章 线性回归

    线性模型形式简单、易于建模,但却蕴涵着机器学习中一些重要的基本思想,许多功能更为强大的非线性模型(nonlinear model)可在线性模型的基础上通过引入层...

    fireWang
  • 利用 Numpy 进行矩阵相关运算

    NumPy 是Python数据分析必不可少的第三方库,NumPy 的出现一定程度上解决了Python运算性能不佳的问题,同时提供了更加精确的数据类型。如今,N...

    fireWang
  • Python官方中文文档尝鲜

    Python官方中文文档地址: https://docs.python.org/zh-cn/

    fireWang
  • Pandas缺失值处理 | 轻松玩转Pandas(3)

    禹都一只猫olei
  • 2016年SDN/NFV开源三大趋势

    在软件定义网络(SDN)和网络功能虚拟化(NFV)开源工作的头几年,开源工作的目标相对模糊,但今年开源工作的进展有了3个明显的趋势。 ? 首先,CORD项目变得...

    SDNLAB
  • 3-STM32物联网开发WIFI(ESP8266)+GPRS(Air202)系统方案基础篇(项目功能演示--GPRS )

    这节演示 STM32采集的温湿度数据通过GPRS模块传给手机APP,APP上有个开关按钮,发指令给GPRS模块,然后发给STM32.

    杨奉武
  • 《笨办法学python》 第14课手记

    《笨办法学Python》 第14课手记 本节课将argv和raw_input和起来使用,作者在之前说,这个组合是个蛮顺手的用法。请注意,引入argv并使用arg...

    Steve Wang
  • scikit-learn 梯度提升树(GBDT)调参小结

        在梯度提升树(GBDT)原理小结中,我们对GBDT的原理做了总结,本文我们就从scikit-learn里GBDT的类库使用方法作一个总结,主要会关注调参...

    刘建平Pinard
  • 面向对象多继承,网络编程,编写网络相关程

    py3study
  • Python:网络编程

    我将通过示例展示如何使用 Python 来编写以各种方式使用网络(如互联网)的程序。Python 提供了强大的网络编程的支持,有很多库实现了常见的网络协议以及基...

    不可言诉的深渊

扫码关注云+社区

领取腾讯云代金券