tolrahC tolrahC - 6 months ago 24
SQL Question

Datediff in seconds overflow when under 68 years

I'm missing something but I don't know what it is.

If I run the following query:

select
max(DATEDIFF(SECOND, EventStartTimeStamp_UTC0, EventEndTimeStamp_UTC0)) as EventDuration
from allEvent


I'm getting the following 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.


I know that using datediff with seconds can give this error when the 2 datetimes are more than 68 years apart. This should not be my case since the oldest data in the table is january 2013.

I ran the following query to ensure that there is no Null DateTime

select count(*)
from allEvent
where EventStartTimeStamp_UTC0 is null or EventStartTimeStamp_UTC0 is null


No record has null DateTime

If I add the following where clause to the query it works

select DATEDIFF(second, EventStartTimeStamp_UTC0, EventEndTimeStamp_UTC0) as EventDuration
from allEvent
where DATEDIFF(Minute, EventStartTimeStamp_UTC0, EventEndTimeStamp_UTC0) >= 1


This doesn't work:

select DATEDIFF(second, EventStartTimeStamp_UTC0, EventEndTimeStamp_UTC0) as EventDuration
from allEvent
where DATEDIFF(Minute, EventStartTimeStamp_UTC0, EventEndTimeStamp_UTC0) < 1


Thanks for your help

Answer

So the oldest is january 2013?

select max(EventStartTimeStamp_UTC0), min(EventStartTimeStamp_UTC0) 
     , max(EventEndTimeStamp_UTC0)  , min(EventEndTimeStamp_UTC0)   
from table