解析SQL Server中datetimeset转换datetime类型问题_MsSql

来源:脚本之家  责任编辑:小易  

在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')

到此这篇关于SQL Server中datetimeset转换datetime类型问题浅析的文章就介绍到这了,更多相关SQL Server中datetimeset转换datetime类型内容请搜索真格学网以前的文章或继续浏览下面的相关文章希望大家以后多多支持真格学网!

您可能感兴趣的文章:SQLSERVER 中datetime 和 smalldatetime类型分析说明sqlserver和oracle中对datetime进行条件查询的一点区别小结sql server中datetime字段去除时间代码收藏sql server中datetime字段去除时间的语句

  • 本文相关:
  • sqlserver 查询时日期格式化语句
  • 解决sql server数据库备份时出现“操作系统错误5(拒绝访问)。b
  • sql中varchar和nvarchar的区别与使用方法
  • 总结一周内学习的sql经验(一)
  • sql server 的sql语句优化方式小结
  • 跨数据库实现数据交流
  • sql 判断数据库,表,存储过程等是否存在的代码
  • sql server 数据库备份和还原认识和总结(二)
  • sql mssql 常用代码大全
  • sql server的链接服务器技术小结
  • MSSQL数据库更改datetimes里面的所有数据日期
  • mssql 怎么将datetime类型转换 成字符串?
  • sql server 中怎么把字符串转化为日期?
  • sqlserver如何比较两个日期(datetime)的年月大小...
  • sql update 如何更新日期型数据
  • sql server 数据库 datetime 获取当前时间 精确到小时
  • sqlserver中如何取日期的年月
  • sql server 日期时间 存储时怎么能不让它显示毫秒...
  • 如何MSSQL触发器当插入数据为20130000到datetime字...
  • 怎么设置sql server时间类型datetime的默认值的呢
  • 网站首页网页制作脚本下载服务器操作系统网站运营平面设计媒体动画电脑基础硬件教程网络安全mssqlmysqlmariadboracledb2mssql2008mssql2005sqlitepostgresqlmongodbredisaccess数据库文摘数据库其它首页mssqlsqlserver 中datetime 和 smalldatetime类型分析说明sqlserver和oracle中对datetime进行条件查询的一点区别小结sql server中datetime字段去除时间代码收藏sql server中datetime字段去除时间的语句sqlserver 查询时日期格式化语句解决sql server数据库备份时出现“操作系统错误5(拒绝访问)。bsql中varchar和nvarchar的区别与使用方法总结一周内学习的sql经验(一)sql server 的sql语句优化方式小结跨数据库实现数据交流sql 判断数据库,表,存储过程等是否存在的代码sql server 数据库备份和还原认识和总结(二)sql mssql 常用代码大全sql server的链接服务器技术小结sql server 2012 安装图解教程(附sql server数据库入门学习总结microsoft sql server 2012 数据win7系统安装sqlserver2000的详细sql重复记录查询的几种方法sqlserver中distinct的用法(不重sql server错误代码大全及解释(sql-order by 多字段排序(升序、sql将一个表中的数据插入到另一个用sql语句添加删除修改字段、一些在sql触发器或存储过程中获取在程序登录的sql server的一个有趣的bit位运算分享sql 取代游标的写法示例.net下执行sqlcmd的方法cpquery 解决拼接sql的新方法sql server 日期相关资料详细介绍mssql存储过程学习笔记一 关于存储过程oracle 删除用户和表空间详细介绍如何恢复数据库备份到一个已存在的正在使sql语句的并集union 交集join(内连接,外
    免责声明 - 关于我们 - 联系我们 - 广告联系 - 友情链接 - 帮助中心 - 频道导航
    Copyright © 2017 www.zgxue.com All Rights Reserved