• 5 Subscribers

I have a table with two dates and a float value that is the difference of the two dates in seconds (with 1 decimal point.)

This is what i am using:

CREATE TABLE [dbo].[table](
    --timestamp and id
    [start] datetime    NULL,
    [end]   datetime    NULL,
    [dif]   AS          ROUND(datediff(millisecond,[start],[end])/1000.0,1)

Apart from this being relatively large it doesn't seem like it's very efficient. Is there a better to do the same thing? like: datediff(secondtenth,[start],[end])/10.0

2 answers in total

Cato Posted at 2017-01-11 13:02:50Z

Here is an example that gets around some potential overflow problems, any gap of more than approx 23 days will give datediff an overflow it seems, but this would would work for decades

declare @start as datetime;
declare @end as datetime;

set @start = '20100701 10:10:10.125';
set @end = '20100702 10:10:10.225';

select round((cast(@end as float) - cast(@start as float)) * 24 * 3600, 1);


with this test I see almost no difference in performance, this is for 10 million calculations, both sets take 13 seconds on my PC

declare @start as datetime;
declare @end as datetime;
DECLARE @MS as float;
DECLARE @I as int;

SET @I = 0;

SET @START = getdate();

WHILE @I < 10000000

    SET @MS = round((cast(getdate() as float) - cast(@start as float)) * 24 * 3600, 1);
    SET @I = @I + 1;


SET @START = getdate();
SET @I= 0;

WHILE @I < 10000000

    SET @MS = ROUND(datediff(millisecond,@start,getdate())/1000.0,1)
    SET @I = @I + 1;

marcothesane Posted at 2017-01-11 13:01:18Z

This is not SQL Server specific, but ANSI SQL, and supported by a lot of DBMS-s.

First - DATETIMEs are usually not the data types to get fractions of seconds. I'd use TIMESTAMP for this, with the default scale of 6 (microsecond), as from the ANSI standard.

Having said that, we also have TIMESTAMPDIFF() in addition to DATEDIFF(). But the datepart possible is either second or millisecond or microsecond. So tough luck here.

Workaround is to divide the integer returned by TIMESTAMPDIFF() by 100 and hard-cast that result back to integer. Now some DBMSs truncate to FLOOR(number)::INTEGER a number with decimal fractions when doing that. If MS SQL does that, just add 0.5 to the division by 100 before hard-casting. It is in any case much, much faster than a ROUND().

And I use the_start and the_end as column names to avoid reserved words and remain within the ANSI standard. (liberté, égalité, portabilité ...)

See here:

WITH foo(the_start,the_end) AS (
          SELECT TIMESTAMP '2017-01-11 07:31:26.016270',TIMESTAMP '2017-01-11 07:31:43.190093'
UNION ALL SELECT TIMESTAMP '2017-01-11 07:31:51.952073',TIMESTAMP '2017-01-11 07:31:52.091006'
UNION ALL SELECT TIMESTAMP '2017-01-11 07:32:10.305528',TIMESTAMP '2017-01-11 07:32:35.460201'
UNION ALL SELECT TIMESTAMP '2017-01-11 07:32:35.460201',TIMESTAMP '2017-01-11 07:32:35.599238'
, the_end
, TIMESTAMPDIFF(millisecond, the_start,the_end) AS diff_ms
, CAST(TIMESTAMPDIFF(millisecond, the_start,the_end)/100 AS INT) AS diff_tenth_sec
FROM foo;
the_start                 |the_end                   |diff_ms|diff_tenth_sec
2017-01-11 07:31:26.016270|2017-01-11 07:31:43.190093| 17,174|           172
2017-01-11 07:31:51.952073|2017-01-11 07:31:52.091006|    139|             1
2017-01-11 07:32:10.305528|2017-01-11 07:32:35.460201| 25,155|           252
2017-01-11 07:32:35.460201|2017-01-11 07:32:35.599238|    139|             1

Happy playing - Marco the Sane

Answer this questsion