SQL Server转Snowflake：日期时间转换报错求助

错误根源 #

你的Snowflake代码存在两个核心问题：

1. 类型不匹配：错误地将子查询结果和默认值用TO_DATE转换为日期类型，导致TEMP.DATETIME（TIMESTAMP类型）与日期做减法，得到的是天数间隔，而非SQL Server中datetime相减得到的时间间隔，完全破坏了原逻辑。
2. 默认值处理错误：用TO_DATE('1900-01-01 06:00:00.000')得到的是日期类型，无法正确模拟SQL Server中CAST('6:00' AS DATETIME)作为时间偏移的逻辑。

修正思路 #

SQL Server的原逻辑是：用原始时间减去一个时间偏移量（要么是班次的起始时间，要么是固定的6小时），再提取日期。在Snowflake中需严格对齐这个逻辑：

• 确保减法两边是TIMESTAMP和TIME类型，相减后得到偏移后的TIMESTAMP，再转DATE。
• 用TIME '06:00:00'替代日期类型的默认值，直接表示6小时的时间偏移。
• 子查询中的FromTimeOfDay需转为TIME类型（若为字符串则用TO_TIME转换）。

修正后的ProductionDate字段代码 #

TO_DATE(
    TEMP.DATETIME - IFNULL(
        (SELECT MIN(TO_TIME(s_first.FromTimeOfDay)) FROM Shift s_first WHERE s_first.FromDay = s.FromDay AND s_first.ShiftCalendarID = s.ShiftCalendarID),
        TIME '06:00:00'
    )
) AS ProductionDate

若FromTimeOfDay字段本身已为TIME类型，可去掉TO_TIME转换。

完整迁移后的SQL代码 #

同时修正了其他SQL Server到Snowflake的函数差异（如DATEADD/DATEPART替换、移除NOLOCK提示等）：

SELECT
    e.Name AS ProductionUnit,
    temp.DateTime AS DateTime,
    s.Reference AS Shift,
    TO_TIME(temp.DateTime) AS Time,
    TO_DATE(
        temp.DateTime - IFNULL(
            (SELECT MIN(TO_TIME(s_first.FromTimeOfDay)) FROM Shift s_first WHERE s_first.FromDay = s.FromDay AND s_first.ShiftCalendarID = s.ShiftCalendarID),
            TIME '06:00:00'
        )
    ) AS ProductionDate,
    temp.ScrapReason AS ScrapReason,
    temp.Quantity AS ScrapQuantity,
    'Manually Registered' AS RegistrationType
FROM (
    SELECT
        CAST(SUM(sreg.ScrapQuantity) AS INTEGER) AS Quantity,
        sreas.Name AS ScrapReason,
        DATEADD(MINUTE, 30 * FLOOR(DATE_PART(MINUTE, sreg.ScrapTime)/30), DATE_TRUNC('HOUR', sreg.ScrapTime)) AS DateTime,
        srer.EquipmentID AS EquipmentID
    FROM qms.ScrapRegistration sreg
    INNER JOIN qms.ScrapReason sreas ON sreas.ID = sreg.ScrapReasonID
    INNER JOIN WorkRequest wr ON wr.ID = sreg.WorkRequestID
    INNER JOIN SegmentRequirementEquipmentRequirement srer ON srer.SegmentRequirementID = wr.SegmentRequirementID
    GROUP BY DATEADD(MINUTE, 30 * FLOOR(DATE_PART(MINUTE, sreg.ScrapTime)/30), DATE_TRUNC('HOUR', sreg.ScrapTime)), srer.EquipmentID, sreas.Name
) temp
INNER JOIN Equipment e ON e.ID = temp.EquipmentID
INNER JOIN ShiftCalendar sc ON sc.ID = dbo.cfn_GetEquipmentShiftCalendarID(e.ID, temp.DateTime)
INNER JOIN Shift s ON s.ID = dbo.cfn_GetShiftIDFromDateTime(temp.DateTime, sc.ID)
UNION
SELECT
    e.Name AS ProductionUnit,
    temp.DateTime AS DateTime,
    s.Reference AS Shift,
    TO_TIME(temp.DateTime) AS Time,
    TO_DATE(
        temp.DateTime - IFNULL(
            (SELECT MIN(TO_TIME(s_first.FromTimeOfDay)) FROM Shift s_first WHERE s_first.FromDay = s.FromDay AND s_first.ShiftCalendarID = s.ShiftCalendarID),
            TIME '06:00:00'
        )
    ) AS ProductionDate,
    temp.ScrapReason AS ScrapReason,
    temp.Quantity AS ScrapQuantity,
    'Auto Registered' AS RegistrationType
FROM (
    SELECT
        SUM(IFNULL(asr.ScrapQuantity, 0)) AS Quantity,
        sreas.Name AS ScrapReason,
        DATEADD(MINUTE, 30 * FLOOR(DATE_PART(MINUTE, asr.ScrapTime)/30), DATE_TRUNC('HOUR', asr.ScrapTime)) AS DateTime,
        srer.EquipmentID AS EquipmentID
    FROM proj.AutoScrapRegistration asr
    INNER JOIN qms.ScrapReason sreas ON sreas.ID = asr.ScrapReasonID
    INNER JOIN WorkRequest wr ON wr.ID = asr.WorkRequestID
    INNER JOIN SegmentRequirementEquipmentRequirement srer ON srer.SegmentRequirementID = wr.SegmentRequirementID
    GROUP BY DATEADD(MINUTE, 30 * FLOOR(DATE_PART(MINUTE, asr.ScrapTime)/30), DATE_TRUNC('HOUR', asr.ScrapTime)), srer.EquipmentID, sreas.Name
) temp
INNER JOIN Equipment e ON e.ID = temp.EquipmentID
INNER JOIN ShiftCalendar sc ON sc.ID = dbo.cfn_GetEquipmentShiftCalendarID(temp.EquipmentID, temp.DateTime)
INNER JOIN Shift s ON s.ID = dbo.cfn_GetShiftIDFromDateTime(temp.DateTime, sc.ID)

额外说明 #

• Snowflake不需要WITH (NOLOCK)提示，其多版本并发控制模型天然避免了锁等待问题。
• 原SQL Server中DATEADD(MINUTE, 30 * (DATEPART(MINUTE, ...)/30), DATEADD(HOUR, DATEDIFF(HOUR,0,...),0))的逻辑，在Snowflake中用DATEADD(MINUTE, 30 * FLOOR(DATE_PART(MINUTE, ...)/30), DATE_TRUNC('HOUR', ...))等价实现，用于将时间向下取整到最近的30分钟间隔。

内容的提问来源于stack exchange，提问作者Mohamed Sharif