当前位置： 首页 > article >正文

SQLServer日期和时间类型

article 2024/11/15 3:07:28

1、本文内容

适用于：

  • Microsoft Fabric Microsoft Fabric
  • Warehouse 中的 SQL Server
  • Azure SQL 数据库
  • Azure SQL 托管实例
  • Azure Synapse
  • Analytics Analytics Platform System （PDW）
  • SQL Analytics 终结点

SQL Server 支持以下日期和时间类型。

  • date (Transact-SQL)
  • datetime (Transact-SQL)
  • datetime2 (Transact-SQL)
  • datetimeoffset (Transact-SQL)
  • smalldatetime (Transact-SQL)
  • time (Transact-SQL)

2、date (Transact-SQL)

2.1、date 说明

propertiesvalue
默认的字符串文字格式yyyy-MM-dd
范围0001-01-01 到 9999-12-31（对于 Informatica，则为 1582-10-15 到 9999-12-31）。公元 1 年 1 月 1 日（公历纪元）到公元 9999 年 12 月 31 日（对于 Informatica，公元 1582 年 10 月 15 日到公元 9999 年 12 月 31 日）。公元 1 年 1 月 1 日（公历纪元）到公元 9999 年 12 月 31 日（对于 Informatica，公元 1582 年 10 月 15 日到公元 9999 年 12 月 31 日）
各元素的范围yyyy 是表示年份的四位数字，范围为从 0001 到 9999。 Informatica 将 yyyy 的范围限制为 1582 到 9999。MM 是表示指定年份中的月份的两位数字，范围为从 01 到 12。dd 是表示指定月份几号的两位数字，介于 01 和 31 之间（具体视月份而定）。
字符长度10 位
精度、小数位数10, 0
存储大小固定 3 个字节
存储结构1 个 3 字节整数存储日期
精确度一天
默认值1900-01-01

2.2、将 date 转换为其他日期和时间类型

此部分介绍了将 date 数据类型转换为其他日期和时间数据类型时会发生什么。

转换为 time(n) 时，转换失败，并引发错误消息 206：

操作数类型冲突: date 与 time 不兼容。

2.2.1、如果转换为 datetime，会复制日期部分。

下面的代码显示将 date 值转换为 datetime 值的结果。

DECLARE @date DATE = '2024-09-20';
DECLARE @datetime DATETIME = @date;

SELECT @date AS '@date', @datetime AS '@datetime';
-- run result 
@date      @datetime
---------- -----------------------
2024-09-20 2024-09-20 00:00:00.000

(1 行受影响)

2.2.2、date 如果转换为 smalldatetime

date 值在 smalldatetime 范围内，日期部分被复制，且时间部分设置为 00:00:00.000。 当 date 值超出 smalldatetime 值的范围时，引发错误消息 242，并且 smalldatetime 值设置为 NULL

DECLARE @date DATE = '2024-09-20';
DECLARE @smalldatetime SMALLDATETIME = @date;

SELECT @date AS '@date',@smalldatetime AS '@smalldatetime';
-- run result 
@date      @smalldatetime
---------- -----------------------
2024-09-20 2024-09-20 00:00:00

(1 行受影响)

2.2.3、转换为 datetimeoffset

转换为 datetimeoffset(n) 时，会复制日期，且时间设置为 00:00.0000000 +00:00。 下面的代码显示将 date 值转换为 datetimeoffset(3) 值的结果。

DECLARE @date DATE = '2024-09-20';
DECLARE @datetimeoffset DATETIMEOFFSET(3) = @date;

SELECT @date AS '@date', @datetimeoffset AS '@datetimeoffset';
-- run result 
@date      @datetimeoffset
---------- ----------------------------------
2024-09-20 2024-09-20 00:00:00.000 +00:00

(1 行受影响)

2.2.4、转换为 datetime2(n) 时

转换为 datetime2(n) 时，会复制日期部分，时间部分设置为 00:00.000000。 下面的代码显示将 date 值转换为 datetime2(3) 值的结果。

DECLARE @date DATE = '2024-09-20'
DECLARE @datetime2 DATETIME2(3) = @date;

SELECT @date AS '@date', @datetime2 AS '@datetime2(3)';
-- run result 
@date      @datetime2(3)
---------- ---------------------------
2024-09-20 2024-09-20 00:00:00.000

(1 行受影响)

2.3、示例将字符串文字转换为 date

如果字符串所有部分的格式都有效，可以将字符串文本转换为日期和时间类型。 否则，将引发运行时错误。 如果从日期和时间类型隐式或显式转换为字符串文本，但未指定样式，将采用当前会话的默认格式。 下表显示用于将字符串文字转换为 date 数据类型的规则。
下例比较了将一个字符串分别转换为各种 date 和 time 数据类型时所产生的结果 。

SELECT
    CAST('2024-09-20 12:35:29.1234567 +12:15' AS TIME(7)) AS 'time',
    CAST('2024-09-20 12:35:29.1234567 +12:15' AS DATE) AS 'date',
    CAST('2024-09-20 12:35:29.123' AS SMALLDATETIME) AS 'smalldatetime',
    CAST('2024-09-20 12:35:29.123' AS DATETIME) AS 'datetime',
    CAST('2024-09-20 12:35:29.1234567 +12:15' AS DATETIME2(7)) AS 'datetime2',
    CAST('2024-09-20 12:35:29.1234567 +12:15' AS DATETIMEOFFSET(7)) AS 'datetimeoffset';
-- run result
time             date       smalldatetime           datetime                datetime2                   datetimeoffset
---------------- ---------- ----------------------- ----------------------- --------------------------- ----------------------------------
12:35:29.1234567 2024-09-20 2024-09-20 12:35:00     2024-09-20 12:35:29.123 2024-09-20 12:35:29.1234567 2024-09-20 12:35:29.1234567 +12:15

(1 行受影响)

3、datetime (Transact-SQL)

用于定义一个与采用 24 小时制并带有秒小数部分的一日内时间相组合的日期。

避免对新工作使用 日期/时间 。 请改用时间、日期、datetime2 和 datetimeoffset 数据类型。 这些类型与 SQL 标准保持一致，并且更易于移植。 time、datetime2 和 datetimeoffset 提供更高精度的秒数 。 datetimeoffset 为全局部署的应用程序提供时区支持 。

3.1、datetime 说明

propertiesvalue
默认字符串文本格式（用于下层客户端）不适用
日期范围1753-01-01 （1753 年 1 月 1 日） 到 9999-12-31 （9999 年 12 月 31 日）
时间范围00:00:00 到 23:59:59.997
时区偏移范围无
元素范围yyyy是表示年份的四位数字17539999。MM 是两个数字，范围从一 01 到 12，表示指定年份中的一个月。dd是两个数字，范围从到0131取决于月份，表示指定月份的一天。HH 是表示小时数的两位数字，范围介于 00 1 到 232 位。mm 是表示分钟数的两位数字，范围从 00 1 到 591。ss 是两个数字，范围从 00 1 到 59，表示第二个数字。n* 为零到三位数字，范围从零 0 到 999三，表示小数秒。
字符长度最低 19 位到最高 23 位
存储大小8 个字节
准确性舍入为增量 .000， .003或 .007 秒
默认值1900-01-01 00:00:00

3.2、将其他日期和时间类型转换为 datetime 数据类型

本部分介绍其他日期和时间数据类型转换为 datetime 数据类型时会发生什么。

3.2.1、date 转换为 DATETIME

从 date 转换时，会复制年、月和日。 时间组件设置为 00:00:00.000. 下面的代码显示将 DATE 值转换为 DATETIME 值的结果。

DECLARE @date DATE = '2024-09-20';
DECLARE @datetime DATETIME = @date;

SELECT @datetime AS '@datetime', @date AS '@date';
-- run result
@datetime               @date
----------------------- ----------
2024-09-20 00:00:00.000 2024-09-20

(1 行受影响)

3.2.2、time转换为DATETIME

当转换从 time（n）开始时，将复制时间组件，并将日期组件设置为 1900-01-01。 当 time（n） 值的分数精度大于三位数时，该值将被截断以适应。 下面的示例显示了将 TIME(4) 值转换为 DATETIME 值的结果。

DECLARE @time TIME(4) = '19:03:21.1237';
DECLARE @datetime DATETIME = @time;

SELECT @datetime AS '@datetime', @time AS '@time';
-- run result
@datetime               @time
----------------------- ----------------
1900-01-01 19:03:21.123 19:03:21.1237

(1 行受影响)

3.2.2、smalldatetime转换为DATETIME

从 smalldatetime 转换时，会复制小时和分钟。 秒和小数秒设置为 0。 下面的代码显示将 SMALLDATETIME 值转换为 DATETIME 值的结果。

DECLARE @smalldatetime SMALLDATETIME = '2024-09-20 19:06';
DECLARE @datetime DATETIME = @smalldatetime;

SELECT @datetime AS '@datetime', @smalldatetime AS '@smalldatetime';
-- run result
@datetime               @smalldatetime
----------------------- -----------------------
2024-09-20 19:06:00.000 2024-09-20 19:06:00

(1 行受影响)

3.2.3、datetimeoffset转换为DATETIME

从 datetimeoffset（n）转换时，将复制日期和时间组件。 时区被截断。 当 datetimeoffset（n） 值的分数精度大于三位数时，该值将被截断。 下面的示例显示了将 DATETIMEOFFSET(4) 值转换为 DATETIME 值的结果。

DECLARE @datetimeoffset DATETIMEOFFSET(4) = '2024-09-20 19:20:59.1234 +10:0';
DECLARE @datetime DATETIME = @datetimeoffset;

SELECT @datetime AS '@datetime', @datetimeoffset AS '@datetimeoffset';
-- run result
@datetime               @datetimeoffset
----------------------- ----------------------------------
2024-09-20 19:20:59.123 2024-09-20 19:20:59.1234 +10:00

(1 行受影响)

3.2.4、datetime2转换为DATETIME

当转换从 datetime2（n）进行时，将复制日期和时间。 当 datetime2（n） 值的分数精度大于三位数时，将截断该值。 下面的示例显示了将 DATETIME2(4) 值转换为 DATETIME 值的结果。

DECLARE @datetime2 DATETIME2(4) = '2024-09-20 19:21:24.1235';
DECLARE @datetime DATETIME = @datetime2;

SELECT @datetime AS '@datetime', @datetime2 AS '@datetime2';
-- run result
@datetime               @datetime2
----------------------- ---------------------------
2024-09-20 19:21:24.123 2024-09-20 19:21:24.1235

(1 行受影响)

3.3、示例字符串分别转换为DATETIME

下例比较了将一个字符串分别转换为 datetime 数据类型时所产生的结果

SELECT CAST('2024-09-20 19:12:49' AS DATETIME) AS 'datetime_cast',CONVERT(DATETIME,'2024-09-20 19:12:59') AS 'datetime_convert';
-- run result
datetime_cast           datetime_convert
----------------------- -----------------------
2024-09-20 19:12:49.000 2024-09-20 19:12:59.000

(1 行受影响)

4、datetime2 (Transact-SQL)

定义结合了 24 小时制时间的日期。 可将 datetime2 视作现有 datetime 类型的扩展，其数据范围更大，默认的小数精度更高，并具有可选的用户定义的精度。

4.1、语法 datetime2

默认的字符串文字格式（用于下级客户端）YYYY-MM-DD hh:mm:ss[.fractional seconds]
日期范围0001-01-01 到 31.12.99，公元 1 年 1 月 1 日到公元 9999 年 12 月 31 日
时间范围00:00:00 到 23:59:59.9999999
时区偏移量范围无
各元素的范围YYYY 是一个四位数，范围从 0001 到 9999，表示年份。MM 是一个两位数，范围从 01 到 12，它表示指定年份中的月份。DD 是一个两位数，范围为 01 到 31（具体取决于月份），它表示指定月份中的某一天。hh 是一个两位数，范围从 00 到 23，它表示小时。mm 是一个两位数，范围从 00 到 59，它表示分钟。ss 是一个两位数，范围从 00 到 59，它表示秒钟。n* 代表 0 到 7 位数字，范围从 0 到 9999999，它表示秒小数部分。 在 Informatica 中，当 n > 3 时，秒的小数部分会被截断。
字符长度最低 19 位 (YYYY-MM-DD hh:mm:ss )，最高 27 位 (YYYY-MM-DD hh:mm:ss.0000000)
精度、小数位数0 至 7 位，准确度为 100ns。 默认精度为 7 位数。在 Microsoft Fabric 中，它可以是 0 到 6 的整数，没有默认值。 必须在 Microsoft Fabric 中指定精度。
存储大小精度小于 3 的 6 个字节。精度为 3 和 4 的 6 个字节。所有其他精度需要 8 个字节。
精确度100 纳秒
默认值1900-01-01 00:00:00

4.2、将其他日期和时间类型转换为 datetime2 数据类型

本部分介绍其他日期和时间数据类型转换为 datetime2 数据类型时会发生什么。

4.2.1、date 转换为 datetime2

从 date 转换时，会复制年、月和日。 时间部分设置为 00:00:00.0000000。 下面的代码显示将 date 值转换为 datetime2 值的结果。

DECLARE @date date = '2024-09-20';
DECLARE @datetime2 datetime2 = @date;

SELECT @datetime2 AS '@datetime2', @date AS '@date';
-- run result
@datetime2                  @date
--------------------------- ----------
2024-09-20 00:00:00.0000000 2024-09-20

(1 行受影响)

4.2.2、time 转换为 datetime2

从 time(n) 转换时，会复制时间部分，日期部分设置为“1900-01-01”。 下面的示例显示了将 time(7) 值转换为 datetime2 值的结果。

DECLARE @time time(7) = '21:18:22.0123315';
DECLARE @datetime2 datetime2 = @time;

SELECT @datetime2 AS '@datetime2', @time AS '@time';
-- run result
@datetime2                  @time
--------------------------- ----------------
1900-01-01 21:18:22.0123315 21:18:22.0123315

(1 行受影响)

4.2.3、datetime 转换为 datetime2

从 datetime 转换时，会复制日期和时间。 小数精度扩展到 7 位。 下面的示例显示了将 datetime 值转换为 datetime2 值的结果。

DECLARE @datetime datetime = '2024-09-20 21:23:38.123';
DECLARE @datetime2 datetime2 = @datetime;

SELECT @datetime2 AS '@datetime2', @datetime AS '@datetime';
-- run result
@datetime2                  @datetime
--------------------------- -----------------------
2024-09-20 21:23:38.1233333 2024-09-20 21:23:38.123

(1 行受影响)

4.2.4、datetimeoffset 转换为 datetime2

从 datetimeoffset(n) 转换时，会复制日期和时间部分。 时区被截断。 下面的示例显示了将 datetimeoffset(7) 值转换为 datetime2 值的结果。

DECLARE @datetimeoffset datetimeoffset(7) = '2024-09-20 21:21:48.1234567 +10:0';
DECLARE @datetime2 datetime2 = @datetimeoffset;

SELECT @datetime2 AS '@datetime2', @datetimeoffset AS '@datetimeoffset'; 
-- run result
@datetime2                  @datetimeoffset
--------------------------- ----------------------------------
2024-09-20 21:21:48.1234567 2024-09-20 21:21:48.1234567 +10:00

(1 行受影响)

4.2.5、smalldatetime 转换为 datetime2

从 smalldatetime 转换时，会复制小时和分钟。 秒和秒的小数部分设置为 0。 下面的代码显示将 smalldatetime 值转换为 datetime2 值的结果。

DECLARE @smalldatetime smalldatetime = '2024-09-20 21:20';
DECLARE @datetime2 datetime2 = @smalldatetime;

SELECT @datetime2 AS '@datetime2', @smalldatetime AS '@smalldatetime'; 
-- run result
@datetime2                  @smalldatetime
--------------------------- -----------------------
2024-09-20 21:20:00.0000000 2024-09-20 21:20:00

(1 行受影响)

4.3、示例字符串分别转换为datetime2

下例比较了将一个字符串分别转换为datetime2 数据类型的结果 。

SELECT 
    CAST('2024-09-20 21:29:39.0123456' AS DATETIME2) AS 'datetime2_cast',
	CONVERT(DATETIME2,'2024-09-20 21:29:39.0123456') AS 'datetime2_convert';
-- run result
datetime2_cast              datetime2_convert
--------------------------- ---------------------------
2024-09-20 21:29:39.0123456 2024-09-20 21:29:39.0123456

(1 行受影响)

5、smalldatetime (Transact-SQL)

定义结合了一天中的时间的日期。 此时间为 24 小时制，秒始终为零 (:00)，并且不带秒小数部分

5.1、smalldatetime 说明

propertiesvalue
默认的字符串文字格式 （用于下级客户端）不适用
日期范围1900-01-01 到 2079-06-06 ，1900 年 1 月 1 日到 2079 年 6 月 6 日
时间范围00:00:00 到 23:59:59，2007-05-09 23:59:59 将舍入为，2007-05-10 00:00:00
各元素的范围YYYY 是表示年份的四位数字，范围为 1900 到 2079。MM 是表示指定年份中的月份的两位数字，范围为 01 到 12。DD 是表示指定月份中的某一天的两位数字，范围为 01 到 31（最高值取决于相应月份）。hh 是表示小时的两位数字，范围为 00 到 23。mm 是表示分钟的两位数字，范围为 00 到 59。ss 是表示秒钟的两位数字，范围为 00 到 59。 值为 29.998 秒或更小值时会向下舍入到最接近的分钟数。 值为 29.999 秒或更大值时会向上舍入到最接近的分钟数。
字符长度最高 19 位
存储大小固定 4 个字节。
精确度一分钟
默认值1900-01-01 00:00:00

5.2、将 smalldatetime 转换为其他日期和时间类型

本部分介绍当 smalldatetime 数据类型转换为其他日期和时间数据类型时发生的情况。

5.2.1、smalldatetime转换成date

转换成 date 时，会复制年、月和日。 下面的代码显示将 smalldatetime 值转换为 date 值的结果。

DECLARE @smalldatetime smalldatetime = '2024-09-20 19:59:27';  
DECLARE @date date = @smalldatetime  
  
SELECT @smalldatetime AS '@smalldatetime', @date AS 'date';
-- run result
@smalldatetime          date
----------------------- ----------
2024-09-20 19:59:00     2024-09-20

(1 行受影响)

5.2.2、smalldatetime转换成time

转换到 time(n) 时，会复制小时、分钟和秒钟。 秒的小数部分设置为 0。 下面的代码显示将 smalldatetime 值转换为 time(4) 值的结果。

DECLARE @smalldatetime smalldatetime = '2024-09-20 20:03:07';  
DECLARE @time time(4) = @smalldatetime;  
  
SELECT @smalldatetime AS '@smalldatetime', @time AS 'time'; 
-- run result
@smalldatetime          time
----------------------- ----------------
2024-09-20 20:03:00     20:03:00.0000

(1 行受影响)

5.2.3、smalldatetime转换成datetime

转换成 datetime 时，会将 smalldatetime 值复制到 datetime 值 。 秒的小数部分设置为 0。 下面的代码显示将 smalldatetime 值转换为 datetime 值的结果。

DECLARE @smalldatetime smalldatetime = '2024-09-20 20:13:26';  
DECLARE @datetime datetime = @smalldatetime;  
  
SELECT @smalldatetime AS '@smalldatetime', @datetime AS 'datetime'; 
-- run result
@smalldatetime          datetime
----------------------- -----------------------
2024-09-20 20:13:00     2024-09-20 20:13:00.000

(1 行受影响)

5.2.4、smalldatetime转换成datetimeoffset

转换成 datetimeoffset(n) 时，会将 smalldatetime 值复制到 datetimeoffset(n) 值。 秒的小数部分设置为 0，时区偏移量设置为 +00:0。 下面的代码显示将 smalldatetime 值转换为 datetimeoffset(4) 值的结果。

DECLARE @smalldatetime smalldatetime = '2024-09-20 20:12:07';  
DECLARE @datetimeoffset datetimeoffset(4) = @smalldatetime;  
  
SELECT @smalldatetime AS '@smalldatetime', @datetimeoffset AS 'datetimeoffset(4)'; 
-- run result
@smalldatetime          datetimeoffset(4)
----------------------- ----------------------------------
2024-09-20 20:12:00     2024-09-20 20:12:00.0000 +00:00

(1 行受影响)

5.2.5、smalldatetime转换成datetime2

转换成 datetime2(n) 时，会将 smalldatetime 值复制到 datetime2(n) 值 。 秒的小数部分设置为 0。 下面的代码显示将 smalldatetime 值转换为 datetime2(4) 值的结果。

DECLARE @smalldatetime smalldatetime = '2024-09-20 20:09:57';  
DECLARE @datetime2 datetime2(4) = @smalldatetime;  
  
SELECT @smalldatetime AS '@smalldatetime', @datetime2 AS ' datetime2(4)';  
-- run result
@smalldatetime           datetime2(4)
----------------------- ---------------------------
2024-09-20 20:10:00     2024-09-20 20:10:00.0000

(1 行受影响)

5.3、示例字符串文字转换为 smalldatetime

5.3.1、将带秒数的字符串文字转换为 smalldatetime

下例比较了将字符串文字中的秒数转换成 smalldatetime 时产生的结果。

SELECT   
     CAST('2024-09-20 19:20:18'     AS smalldatetime) AS v1  
    ,CAST('2024-09-20 19:20:18'     AS smalldatetime) AS v2 
    ,CAST('2024-09-20 19:20:18.998' AS smalldatetime) AS v2;
-- run result 
v1                      v2                      v2
----------------------- ----------------------- -----------------------
2024-09-20 19:20:00     2024-09-20 19:20:00     2024-09-20 19:20:00

(1 行受影响)

http://www.kler.cn/a/314032.html

相关文章：

  • 华为机试HJ39 判断两个IP是否属于同一子网
  • 闯关leetcode——3174. Clear Digits
  • 深度学习——权重初始化、评估指标、梯度消失和梯度爆炸
  • spring中r类是什么
  • Linux第四讲：Git gdb
  • jwt用户登录，网关给微服务传递用户信息，以及微服务间feign调用传递用户信息
  • SpringCloud从零开始简单搭建 - JDK17
  • 基于python上门维修预约服务数据分析系统
  • Apache SeaTunnel Zeta引擎源码解析（三） Server端接收任务的执行流程
  • 【案例72】Apache检测到目标 URL 存在 http host 头攻击漏洞的解决方案
  • 如何安装1Panel面板并架设一个静态网站
  • Kubernetes从零到精通（11-CNI网络插件）
  • ppt组织结构图怎么增加分支？
  • 【python 类型注解详解】
  • 基于神经网络的光线追踪
  • 2024最新！！！iOS高级面试题，全！(一)
  • STM32（十五）：I2C通信
  • PostgreSQL数据库怎么生成一个随机的UUID
  • K8S介绍+集群部署
  • Django视图：构建动态Web页面的核心技术
  • 伊犁职业技术学院linux 部署教学用首先创建两台linux 主机
  • springboot实战学习笔记（2）
  • MyBatis框架SqlSession浅析
  • Elastic 的 OpenTelemetry PHP 发行版简介
  • RealityCapture1.4设置缓存和设置重置
  • Python 二级考试