asp.net - 我想合并日期和时间并与 utc 日期进行比较

Question

当我写这个查询时

SELECT Convert(datetime,Convert(varchar,CAST(GETUTCDATE() AS DATE))+' '+ 
       CONVERT(varchar, cast(meas_pain.datetime AS time))) FROM meas_pain

它对我有用，但是当我在 WHERE 子句中使用相同的部分时，它会给出错误“从字符串转换日期和/或时间时转换失败。”

SELECT schedules.id 
FROM meas_pain LEFT JOIN schedules ON schedules.id=meas_pain.schd_id 
WHERE meas_pain.schd_id=9150 AND  
      Convert(datetime,(Convert(varchar,CAST(GETUTCDATE() AS DATE))+' '+
      CONVERT(varchar, cast(meas_pain.datetime AS time)))) < 
      CONVERT(datetime,DATEADD(Minute,0,getutcdate()))

谁能解释一下吗？

Answer 1

我不确定为什么这个错误没有出现在您的 select 语句中，因为我可以使用以下命令重现该错误

SET DATEFORMAT DMY;
SELECT  CONVERT(DATETIME, CONVERT(VARCHAR,CAST(GETUTCDATE() AS DATE)))

<强> Example of Error

您依赖于本地化转换设置，您应该使用显式转换，例如

SET DATEFORMAT DMY;
SELECT  CONVERT(DATETIME, CONVERT(VARCHAR, CAST(GETUTCDATE() AS DATE), 111), 111)

通过显式定义日期格式以将其转换为 varchar 以及从 varchar (111) 转换，您可以避免任何隐式转换。

但是，如果您的日期/时间是这样存储的，则不需要进行所有与 varchar 之间的转换，这只会增加出现问题的机会和不必要的工作，您只需将时间添加到日期时间即可。例如

DECLARE @Date1 DATETIME = DATEADD(HOUR, 1, GETUTCDATE()),
        @Date2 DATETIME = DATEADD(DAY, 1, GETUTCDATE());

SELECT  [Date1] = @Date1,
        [Date2] = @Date2,
        [Date1/Time2] = CAST(CAST(@Date1 AS DATE) AS DATETIME) +
                           CAST(@Date2 AS TIME);

根据我从您的查询中收集到的信息，您只是想获取 meas_pain.datetime 时间小于当前 UTC 时间的结果，无论日期如何。因此，您应该能够将查询简化为:

SELECT  schedules.id 
FROM    meas_pain 
        LEFT JOIN schedules 
            ON schedules.id = meas_pain.schd_id 
WHERE   meas_pain.schd_id = 9150
AND     CAST(meas_pain.[DateTime] AS TIME) < CAST(GETUTCDATE() AS TIME);

并删除进一步的冗余转换。

<强> Simplified example on SQL Fiddle

附录

显然这次比较不是您想要的(尽管这是您发布的查询正在执行的操作)，所以我假设 GETUTCDATE() 只是为了演示。

您尝试执行的转换相当于:

CAST(CAST(GETUTCDATE() AS DATE) AS DATETIME) + CAST(meas_pain.[DateTime] AS TIME)

<强> Another example on SQL Fiddle using the above conversion