SQL Server中datetimeset转换类型为datetime有啥方法

发布时间：2023-04-21 14:05:30 所属栏目：MsSql教程来源：

导读：这篇文章主要讲解了“SQL Server中datetimeset转换类型为datetime有哪些方法”，文中的讲解内容简单、清晰、详细，对大家学习或是工作可能会有一定的帮助，希望大家阅读完这篇文章能有所收获。下面就请大家

这篇文章主要讲解了“SQL Server中datetimeset转换类型为datetime有哪些方法”，文中的讲解内容简单、清晰、详细，对大家学习或是工作可能会有一定的帮助，希望大家阅读完这篇文章能有所收获。下面就请大家跟着小编的思路一起来学习一下吧。

在SQL Server中，数据类型datetimeoffset转换为datetime类型或datetime2类型时需要特别注意，有可能一不小心你可能会碰到下面这种情况。下面我们构造一个简单案例，模拟一下你们可能遇到的情况。

CREATE TABLE TEST

(

  ID         INT IDENTITY(1,1)

  ,CREATE_TIME    DATETIME

  ,CONSTRAINT PK_TEST PRIMARY KEY(ID)

);

GO

INSERT INTO TEST(CREATE_TIME)

SELECT '2020-10-03 11:10:36' UNION ALL

SELECT '2020-10-03 11:11:36' UNION ALL

SELECT '2020-10-03 11:12:36' UNION ALL

SELECT '2020-10-03 11:13:36';

DECLARE @p1 DATETIMEOFFSET;

SET @p1='2020-10-03 11:12:36.9200000 +08:00'

SELECT * FROM dbo.TEST

WHERE CREATE_TIME <=@p1;

你会发现这个查询SQL查不到任何记录。相信以前对数据类型datetimeoffset不太熟悉的人会对这个现象一脸懵逼......

那么我们通过下面例子来给你简单介绍一下，datetimeoffset通过不同方式转换为datetime有啥区别，具体脚本如下：

DECLARE @p1 DATETIMEOFFSET;

DECLARE @p2 DATETIME;

DECLARE @p3 DATETIME2;

SET @p1='2020-10-03 11:10:36.9200000 +08:00'

SET @p2=@p1;

SET @p3=@p1;

SELECT @p1               AS '@p1'

   ,@p2               AS '@p2'

   ,CAST(@p1 AS DATETIME)      AS 'datetimeoffset_cast_datetime'

   ,CONVERT(DATETIME, @p1, 1)    AS 'datetimeoffset_convert_datetime'

通过CONVERT函数将datetiemoffset转换为datetime,你会发现上面这种方式丢失了时区信息，它将datetimeoffset转换为了UTC时间了。官方文档介绍：转换到datetime 时，会复制日期和时间值，时区被截断。

注意：datetiemoffset转换为datetime2也是同样的情况，这里不做赘述了。

所以，最开始，我们构造的案例中，出现那种现象是因为@p1和CREATE_TIME比较时，发生了隐式转换，datetiemoffset转换为datetime，而且转换过程中时区丢失了，此时的SQL实际等价于CREATE_TIME <='2020-10-03 03:10:36.920'了，那么怎么解决这个问题，如果在不改变数据类型的情况下，有什么解决方案解决这个问题呢？

方案1：使用CAST转换函数。

DECLARE @p1 DATETIMEOFFSET;

SET @p1='2020-10-03 11:12:36.9200000 +08:00'

SELECT * FROM dbo.TEST

WHERE CREATE_TIME <=CAST(@p1 AS DATETIME)

方案2：CONVERT函数中指定date_style为0 ，可以保留时区信息。

DECLARE @p1 DATETIMEOFFSET;

SET @p1='2020-10-03 11:12:36.9200000 +08:00'

SELECT * FROM dbo.TEST

WHERE CREATE_TIME <=CONVERT(DATETIME, @p1, 0)

下面例子演示对比，有兴趣的话，自行执行SQL后对比观察

DECLARE @p1 DATETIMEOFFSET;

DECLARE @p2 DATETIME;

DECLARE @p3 DATETIME2;

SET @p1='2020-10-03 11:10:36.9200000 +08:00'

SET @p2=@p1;

SET @p3=@p1;

SELECT @p1               AS '@p1'

   ,@p2               AS '@p2'

   ,CAST(@p1 AS DATETIME)      AS 'datetimeoffset_cast_datetime'

   ,CONVERT(DATETIME, @p1, 0)    AS 'datetimeoffset_convert_datetime'

   ,CONVERT(DATETIME, @p1, 1)    AS 'datetimeoffset_convert_datetime1'

方案3：SQL Server 2016（13.x）或以后的版本可以使用下面方案。

注意之前的SQL Server版本不支持这种写法.

DECLARE @p1 DATETIMEOFFSET;

SET @p1='2020-10-03 11:12:36.9200000 +08:00'

SELECT * FROM dbo.TEST

WHERE CREATE_TIME <= CONVERT(DATETIME, @p1 AT TIME ZONE 'UTC' AT TIME ZONE 'China Standard Time')

（编辑：聊城站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!