CODEWITHSUNDEEP
sqlsql-server-2008t-sql
Deepa
Deepa

Reputation: 21

Datediff function resulted in an overflow for two date Minute Diff (Without DateDiff_Big)

I am using SQL 2008/2012.

Query to calculate Minute Difference between two dates.

select DATEDIFF(mi, '9999-08-03 04:20:00.000', '2005-05-22 03:45:09.530')

Error:

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.

Note : DateDiff_Big not support this version.

Is there any other way to get result. without using DateDiff_Big

Upvotes: 2

Views: 2395

Answers (5)

Ronen Ariely
Ronen Ariely

Reputation: 2434

Does this fit your needs

DECLARE @DT1 datetime = '9999-08-03 04:20:00.000'
DECLARE @DT2 datetime = '2005-05-22 03:45:09.530'
select --DATEDIFF_BIG(mi, @DT1, @DT2),
    CONVERT(BIGINT, DATEDIFF(DAY, @DT1, @DT2)) * 24 * 60
     + CONVERT(BIGINT, DATEDIFF(mi, CONVERT(TIME(7), @DT1), CONVERT(TIME(7), @DT2)))
GO

Upvotes: 1

LukStorms
LukStorms

Reputation: 29667

DATEDIFF has a limit that depends on what time element is used.
Because it can only return a number that fits in an INT.

For a return value out of range for int (-2,147,483,648 to +2,147,483,647), DATEDIFF returns an error. For millisecond, the maximum difference between startdate and enddate is 24 days, 20 hours, 31 minutes and 23.647 seconds. For second, the maximum difference is 68 years.

For minutes that appears to be 4083 years.

So then limit for minutes would be in the -4083 years to 4083 years range.
Then you could wrap the DATEDIFF in a CASE WHEN that checks if it's in that range.

And let it default to the DATEDIFF in hours * 60.
It'll loose some minutes for the default, but that might still be better than returning a NULL. 

SELECT dt1, dt2,
DATEDIFF(year, dt1, dt2) AS diff_years,
DATEDIFF(hour, dt1, dt2) AS diff_hours,
CONVERT(BIGINT,
 CASE 
 WHEN DATEDIFF(year, dt1, dt2) BETWEEN -4083 AND 4083 
 THEN DATEDIFF(minute, dt1, dt2)
 ELSE CONVERT(BIGINT,DATEDIFF(hour, dt1, dt2)) * 60
 END) AS diff_minutes
FROM (VALUES 
 ('9999-08-03 04:20:00.000', '2005-05-22 03:45:09.530'),
 ('6083-01-01 00:00:00.000', '2000-01-01 00:00:00.000'),
 ('2000-01-01 00:00:00.000','6083-01-01 00:00:00.000'),
 ('0001-01-01 00:00:00.000','9999-12-31 00:00:00.000')
) q(dt1, dt2)

Upvotes: 0

Mr. Bhosale
Mr. Bhosale

Reputation: 3106

Check This.

SELECT 
        CAST(DATEDIFF(hour, '9999-08-03 04:20:00.000', '2009-05-22 03:45:09.530') AS BIGINT)* 60+ 
        DATEDIFF(mi, CONVERT(TIME, '9999-08-03 04:20:00.000'), 
        CONVERT(TIME, '2009-05-22 03:45:09.530'))+60;

Upvotes: 0

ravi polara
ravi polara

Reputation: 572

try this type to get Minutes Different

SELECT CONVERT(BIGINT, DATEDIFF(HOUR, '2005-05-22 03:45:09.530', '9999-08-03 04:20:00.000')) * 60

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269953

So you use a smaller unit and do some arithmetic. But I presume you mean:

select datediff(minute, '2005-05-22 03:45:09.530', '9999-08-03 04:20:00.000')

Normally, one wants the difference to be positive (although that is not related to the answer).

select (convert(bigint, datediff(day, '2100-08-03 04:20:00', '9999-08-03 04:20:00.000')) * 60 * 24) +
    datediff(minute, '2005-05-22 03:45:09.530', '2100-08-03 04:20:00')

Upvotes: 1

Related Questions