DATEDIFF overflow

Question

I am using the following code in azure sql datawarehouse

SELECT cast(DATEDIFF(ms,cast(Start as datetime2),cast(EndTime as datetime2)
                        ) as float) AS [total]--difference to be calculated in millisecond
        FROM systable

but coming across an error as "The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart. "

My requirement is to have the difference in milliseconds and if thats changed then it will affect other results.

request you to please provide some help

score 0 · Answer 1 · answered Jan 30 '20 at 17:49

This happens because the DATEDIFF() function returns an integer. An integer only allows values up to 2,147,483,647. In this case, you have more than ~2B values causing the data type overflow. You would ideally use the DATEDIFF_BIG() function which returns a bigint that allows for values up to 9,223,372,036,854,775,807 or ~9 Septillion. DATEDIFF_BIG() isn't supported in SQL Data Warehouse / Azure Synapse Analytics (as of Jan 2020).

You can vote for the feature here: (https://feedback.azure.com/forums/307516/suggestions/14781627)

Testing DATEDIFF(), you can see that you can get ~25 days and 20 hours of difference between dates before you run out of integers. Some sample code is below.

DECLARE @startdate DATETIME2 = '01/01/2020 00:00:00.0000';
DECLARE @enddate DATETIME2   = '01/01/2020 00:00:02.0000';

-- Support:
--   MILLISECOND:    ~25 days 20 Hours
--   MICROSECOND:    ~35 minutes
--   NANOSECOND:     ~ 2 seconds

SELECT
    DATEDIFF(DAY, @startdate, @enddate)             [day]
    , DATEDIFF(HOUR, @startdate, @enddate)          [hour]
    , DATEDIFF(MINUTE, @startdate, @enddate)        [minute]
    , DATEDIFF(SECOND, @startdate, @enddate)        [second]
    , DATEDIFF(MILLISECOND, @startdate, @enddate)   [millisecond]
    , DATEDIFF(MICROSECOND, @startdate, @enddate)   [microsecond]
    , DATEDIFF(NANOSECOND, @startdate, @enddate)    [nanosecond]

In the interim, you could calculate the ticks since the beginning of the time for each value and then subtract the difference. For a DATETIME2, you can calculate ticks like this:

CREATE FUNCTION dbo.DATEDIFF_TICKS(@date DATETIME2)
RETURNS BIGINT
AS
BEGIN

    RETURN
        (DATEDIFF(DAY, '01/01/0001', CAST(@date AS DATE)) * 864000000000.0)
        + (DATEDIFF(SECOND, '00:00', CAST(@date AS TIME(7))) * 10000000.0)
        + (DATEPART(NANOSECOND, @date) / 100.0);
END
GO

You can then just run the function and determine the ticks and the difference between ticks.

DECLARE @startdate DATETIME2 = '01/01/2020 00:00:00.0000';
DECLARE @enddate DATETIME2 = '01/30/2020 00:00:00.0000';

SELECT
    dbo.DATEDIFF_TICKS(@startdate)      [start_ticks],
    dbo.DATEDIFF_TICKS(@startdate)      [end_ticks],
    dbo.DATEDIFF_TICKS(@enddate) - dbo.DATEDIFF_TICKS(@startdate) [diff];

Here is a sample running 500 years of differences:

DECLARE @startdate DATETIME2 = '01/01/2000 00:00:00.0000';
DECLARE @enddate DATETIME2 = '01/01/2500 00:00:00.0000';

SELECT
    dbo.DATEDIFF_TICKS(@startdate)      [start_ticks],
    dbo.DATEDIFF_TICKS(@startdate)      [end_ticks],

    dbo.DATEDIFF_TICKS(@enddate) - dbo.DATEDIFF_TICKS(@startdate) [diff];

The results:

start_ticks          end_ticks            diff
-------------------- -------------------- --------------------
630822816000000000   630822816000000000   157785408000000000

DATEDIFF overflow

1 Answers1