SQL Datediff in seconds with decimal places

Question

I am trying to extract the difference between two SQL DateTime values in seconds, with decimal places for some performance monitoring.

I have a table, "Pagelog" which has a "created" and "end" datetime. In the past I have been able to do the following:

SELECT DATEDIFF(ms, pagelog_created, pagelog_end)/1000.00 as pl_duration FROM pagelog

However I have started getting the following error:

Msg 535, Level 16, State 0, Line 1
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 have seen numerous responses to this error stating that I should use a less precise unit of measurement. But this hardly helps when I need to distinguish between 2.1 seconds and 2.9 seconds, because DATEDIFF(s,..,..) will return INT results and lose the accuracy I need.

I originally thought that this had been caused by a few values in my table having a huge range but running this:

SELECT DATEDIFF(s, pagelog_created, pagelog_end) FROM pagelog
ORDER BY DATEDIFF(s, pagelog_created, pagelog_end) DESC

Returns a max value of 30837, which is 8.5 hours or 30,837,000 milliseconds, well within the range of a SQL INT as far as I know?

Any help would be much appreciated, as far as I can tell I have two options:

• Somehow fix the problem with the data, finding the culprit values
• Find a different way of calculating the difference between the values

Thanks!

Answer 1

The StackOverflow magic seems to have worked, despite spending hours on this problem last week, I re-read my question and have now solved this. I thought I'd update with the answer to help anyone else who has this problem.

The problem here was not that there was a large range, there was a negative range. Which obviously results in a negative overflow. It would have been helpful if the SQL Server error was a little more descriptive but it's not technically wrong.

So in my case, this was returning values:

SELECT * FROM pagelog
WHERE pagelog_created > pagelog_end

Either remove the values, or omit them from the initial result set!

Thanks to Ivan G and Andriy M for your responses too