主页 / dba / 问题 / 71150
Accepted
Simon Hughes
Asked: 2014-07-11 01:44:12 +0800 CST

SQL Datetime / datetimeoffset(2) 比较 & 隐式转换奇怪

  • 772

在我们的数据库中插入数据时，我发现了一个问题。我的插入语句正在检查 WHERE 子句中是否存在数据，以防止插入重复数据。没有检测到，并且发生了 INSERT。但是，唯一约束拒绝了数据，因为它已经存在于数据库中。

问题是要插入的数据是 DATETIMEOFFSET(2)，而要插入的数据库字段是 DATETIME。

为了表明你想要我在说什么，运行以下命令：

DECLARE @dt  DATETIME          = '2014-07-07 09:49:33.000';
DECLARE @dto DATETIMEOFFSET(2) = '2014-07-07 09:49:33.00 +07:00';

PRINT CASE WHEN @dt = @dto THEN 'Equals matches'
           ELSE 'Equals does not match'
      END

PRINT CASE WHEN @dt = CAST(@dto AS DATETIME) THEN 'Cast matches'
           ELSE 'Cast does not match'
      END

它打印：

  • 等于不匹配
  • 演员匹配

如果插入数据，比较 (=) 运算符的执行方式与隐式强制转换不同。cast/convert 运算符实际上丢弃了偏移量！疯狂。

为什么比较运算符与 INSERT 期间发生的隐式转换的工作方式不同？

sql-server

2 个回答

  1. Best Answer

    看起来相反的是真的：隐式转换将偏移量带入方程，但转换/转换函数没有。

    DECLARE @dt  DATETIME          = '2014-07-07 02:49:33.000';
DECLARE @dto DATETIMEOFFSET(2) = '2014-07-07 09:49:33.000 +07:00';

PRINT CASE WHEN @dt = @dto THEN 'Equals matches'
           ELSE 'Equals does not match'
      END

PRINT CASE WHEN @dt = CAST(@dto AS DATETIME) THEN 'Cast matches'
           ELSE 'Cast does not match'
      END

PRINT CASE WHEN @dt = CONVERT(DATETIME,@dto) THEN 'Convert matches'
           ELSE 'Convert does not match'
      END

    比较这个（从@dt 中扣除 7 小时）的结果是：

    • 等于匹配
    • 演员表不匹配
    • 转换不匹配

    做了一些更多的调查，偶然发现了这篇文章。

    “当您从 datetime2 或 datetimeoffset 转换为 date 时，没有四舍五入，并且日期部分被显式提取。对于从 datetimeoffset 到 date、time、datetime2、datetime 或 smalldatetime 的任何隐式转换，转换基于本地日期和时间价值。”

    因此，当您想将 '2014-07-07 09:49:33.000' 和 '2014-07-07 09:49:33.000 +07:00' 视为相等时，您唯一的选择是通过强制转换或兑换。因为隐式转换仅在您的服务器的时区偏移量恰好与@dto 中指定的偏移量相同时才起作用。

    • 10

  2. 好吧，比较和 CAST 的工作方式不同。

    比较使用类型优先级来确定如何比较不同类型。DATETIMEOFFSET分别排在第4位，DATETIME第DATETIME26位和第5位。所以DATETIME参数被转换（使用CAST，见下文）DATETIMEOFFSET首先。

    转换为DATETIMEOFFSET可以通过三种可能的方式实现（也许更多？）：

    • CAST添加“+00:00”时区（UTC）
    • CONVERT添加“+00:00”时区（UTC）
    • AT TIMEZONE它将您提供的任何时区添加为右侧参数

    可悲的是，我从来没有找到任何CAST关于“CONVERT升级”到DATETIMEOFFSET. 所以这种行为可能在机器和版本之间有所不同。

    另一方面，转换为DATETIME 砍掉“偏移”部分。

    所以，在你的情况下：

    @dt = @dto
  -- @dt gets converted to DATETIMEOFFSET(2)
  -- you are comparing:
  -- '2014-07-07 09:49:33.000 +00:00' = '2014-07-07 09:49:33.00 +07:00'

@dt = CAST(@dto AS DATETIME)
  -- botha are DATETIME, but @dto gets "downscaled" to DATETIME
  -- you are comparing:
  -- '2014-07-07 09:49:33.000' = '2014-07-07 09:49:33.00'
    • 2

相关问题