parthi_sql avatar image
parthi_sql asked

DateDiff Overflow

Hi, I'm getting the below error, while fetching report for a month, with SQL date check eg.. datediff(second, starttime, endtime)>3600 **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.**
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

Wilfred van Dijk avatar image
Wilfred van Dijk answered
My first reaction was "try DATEDIFF_BIG() function instead". However , are you assigning the result of this datediff to a correct datatype?
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Oleg avatar image Oleg commented ·
@parthi_sql You might also want to check the values of the starttime and endtime. There are 31.5 million seconds per year, the maximum int value is about 2.147 billion so the dates have to be more than 68 years apart for the datediff to break. Perhaps there are starttime values of 0 (meaning 1900-01-01) or the endtime values well into the future. DATEDIFF\_BIG() function is available in SQL Server 2016, not in any earlier versions, so if your instance is not 2016 then the function will not work and you will need to use something else. For example, cut the check short if the dates are further apart then 68 years (or some whatever smaller positive number) and only then check for datediff in seconds, i.e. datediff(year, starttime, endtime) > 68 or datediff(second, starttime, endtime) > 3600 in place of the original datediff(second, starttime, endtime) > 3600 You can replace number 68 with any smaller positive number. Relying on short circuits is generally ill-advised, but in this specific case, the engine will use the short circuit to not even bother checking for datediff in seconds if the first condition is already true.
2 Likes 2 ·
Oleg avatar image Oleg commented ·
@parthi_sql Another possibility is to simply use **endtime - starttime**. Certainly, the datediff in seconds cannot be substituted by datediff in hours because this would be silly and guaranteed to produce incorrect results. For example, if the difference between the dates is 1 hour 59 minutes, the **datediff(hour, starttime, endtime) > 1** will be false but the desired result is true. When the endtime - starttime is used then the difference is the datetime value, i.e. the difference between today and day before yesterday same time is January 3 1900. In order to avoid any possibility of the overflows the line reading datediff(second, starttime, endtime)>3600 may be replaced with the line reading endtime - starttime > '1900-01-01 01:00:00.000' If the data type of starttime and endtime is datetime and the endtime is always greater than starttime (which it should be) then this will always work as expected with no possibilities for any overflows.
1 Like 1 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.