Azure SQL数据仓库DATEDIFF(ms)溢出问题求助

Got it, I totally get why you can’t just switch to a less precise datepart—your downstream dependencies rely on that millisecond-level data, and messing with that isn’t an option. The error pops up because DATEDIFF(ms) has a hard limit: it can only handle time differences up to ~24.8 days (since the result is an int, maxing out at 2^31-1 milliseconds). For larger time spans, we need workarounds that keep that critical millisecond precision intact.

Here are two reliable solutions you can implement right away:

Solution 1: Leverage Datetime2’s Numeric Storage #

Azure SQL Data Warehouse stores datetime2 values as counts of 100-nanosecond intervals since a base date. We can use this underlying representation to calculate the full time difference without hitting the DATEDIFF overflow:

SELECT 
    CAST(
        (CAST(EndTime AS BIGINT) - CAST(Start AS BIGINT)) / 10000.0 
        AS FLOAT
    ) AS [total]
FROM systable

How this works: #

• Casting a datetime2 to BIGINT gives you the total number of 100-nanosecond "ticks" between the date and the SQL epoch.
• Subtracting the start ticks from the end ticks gives the total ticks between your two timestamps.
• Dividing by 10000 converts those ticks to milliseconds (since 1 millisecond = 10,000 x 100-nanosecond ticks).
• Casting to FLOAT preserves any fractional millisecond values if your timestamps have sub-millisecond precision.

Solution 2: Break the Calculation into Smaller, Safe Intervals #

If you prefer sticking with familiar datepart functions (for readability or consistency with existing code), split the time difference into smaller chunks that won’t trigger an overflow, then sum them up in milliseconds:

SELECT
    CAST(
        -- Days converted to milliseconds
        DATEDIFF(DAY, Start, EndTime) * 86400000.0 +
        -- Remaining hours after full days, converted to milliseconds
        DATEDIFF(HOUR, DATEADD(DAY, DATEDIFF(DAY, Start, EndTime), Start), EndTime) * 3600000.0 +
        -- Remaining minutes after full hours, converted to milliseconds
        DATEDIFF(MINUTE, DATEADD(HOUR, DATEDIFF(HOUR, Start, EndTime), Start), EndTime) * 60000.0 +
        -- Remaining seconds after full minutes, converted to milliseconds
        DATEDIFF(SECOND, DATEADD(MINUTE, DATEDIFF(MINUTE, Start, EndTime), Start), EndTime) * 1000.0 +
        -- Final remaining milliseconds
        DATEDIFF(MILLISECOND, DATEADD(SECOND, DATEDIFF(SECOND, Start, EndTime), Start), EndTime)
        AS FLOAT
    ) AS [total]
FROM systable

How this works: #

• We start with the largest safe interval (days) which will never overflow even for years of time difference, convert to milliseconds.
• Then calculate the remaining hours after those full days, convert to milliseconds.
• Repeat for minutes, seconds, and finally the leftover milliseconds.
• Summing all these gives the exact total millisecond difference, and using FLOAT ensures we don’t lose precision.

Both methods will give you the millisecond-level accuracy you need, even for extremely large time spans, without triggering the overflow error.

内容的提问来源于stack exchange，提问作者Pradyot Mohanty