VB.net 2010 视频教程 VB.net 2010 视频教程 python基础视频教程
SQL Server 2008 视频教程 c#入门经典教程 Visual Basic从门到精通视频教程
当前位置:
首页 > 数据库 > T-SQL >
  • SQL Server中datetimeset转换datetime类型问题浅析

2020-12-28 15:55  潇湘隐者  阅读(573)  评论(0)  编辑  收藏  举报
在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不太熟悉的人会对这个现象一脸懵逼......
 
clip_image001
 
那么我们通过下面例子来给你简单介绍一下,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也是同样的情况,这里不做赘述了。
clip_image002
 
所以,最开始,我们构造的案例中,出现那种现象是因为@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'
 
clip_image003
 
方案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')
 
出处:https://www.cnblogs.com/kerrycode/p/14202017.html

相关教程