Creating a Duration Function in SQL Server

Question

I am trying to create a function which will return the whole number of periods that have elapsed between two dates, NOT the number of boundaries crossed.

As an example myFunc(Hour, '01:31','03:20') = 1 (would become 2 at "03:31")

I am running into 3 issues:

Date calculations are complicated and I would like to keep my code neat and as simple as possible, so I'd like to use built-in date function instead of reinventing the Gregorian calendar.
DateDiff takes a datepart argument, which has resisted all my attempts to pass as a variable, except via dynamic sql.
I need to call this in a select statement so it can't be a stored procedure, but functions in SQL refuse to run either dynamic SQL or stored procedures.
(I ran into a couple issues with overloading DateDiff since I am running SSMS 2012 and therefore don't have DateDiff_BIG)

Here is my code:

    [dbo].[DDif]
(
    @Start datetime
    ,@End datetime
    ,@Period varchar = null
)
RETURNS bigint
Declare @Dif bigint
if @Period is null set @Period = 'Y'
@p varchar(20) = case
    when @Period = 'Y' then 'yy'
    when @Period in('S','Q') then 'q'
    when @Period = 'M' then 'm'
    when @Period in('B','W') then 'ww'
    when @Period = 'D' then 'd' 
    when @Period = 'h' then 'hh'
    when @Period = 'm' then 'n'
    when @Period = 's' then 's'
End
@sQry varchar(8000) = 'Set @Dif = DateDiff('+@p+',@Start,@End) + case when DateAdd('+@p+',DateDiff('+@p+',@Start,@End),@Start)>@End then -1 else 0 end'
execute @sQry
if @Period in ('S','B') return @Dif/2
return @Dif

I am looking for any one of 3 solutions:

A non-dynamic way to pass dateparts as variables
A way to run dynamic SQL from a function
A clean/simple implementation of a different approach to calculating duration between two datetimes for a user-specified period length.

Note: Had to remove a few small parts of my code that stack overflow didn't like, so I know that it won't run exactly as it appears.

Edit: Thank you to Mark for the solution. Here is my current implementation:

FUNCTION [dbo].[DDif]
(
    @Start datetime
    ,@End datetime
    ,@Period varchar = null
)
RETURNS bigint
AS
BEGIN
    if @Period is null set @Period = 'Y'
    declare @m int = DATEDIFF(Month,@Start,@End) + case when DATEADD(MONTH,DATEDIFF(Month,@Start,@End),@Start)>@End then -1 else 0 END
    declare @s bigint = 86400*DATEDIFF(DAY, CAST(@Start as date),CAST(@End as date))+datediff(s,cast(@Start as time),cast(@End as time))
    declare @num bigint = case 
        when @Period = 'Y' then 12
        when @Period = 'S' then 6
        when @Period = 'Q' then 3
        when @Period = 'M' then 1
        when @Period = 'B' then 1209600
        when @Period = 'W' then 604800
        when @Period = 'D' then 86400
        when @Period = 'h' then 3600
        when @Period = 'm' then 60
        when @Period = 's' then 1
    end
    return case
        when @Period in ('Y','S','Q','M') then @m
        when @Period in ('B','W','D','h','m','s') then @s
    end / @num

As far as I can tell, you could just do DATEDIFF(???, 0, RightDateTime - LeftDateTime)... Such that your example would be DATEDIFF(Hour, 0, '03:20' - '01:31')? (May need explicit CAST to DATETIME when using string literals though) — MatBailie
– MatBailie, Commented Jan 3, 2017 at 16:00
using DATEDIFF with a CASE and DATETIME2 datatype is the best option, both for precision and ease of use. please see my answer. — MtwStark
– MtwStark, Commented Jan 3, 2017 at 16:19
Well, I already mentioned this in comments on the answer, but since the comments here would otherwise mislead I'll call it out here too: According to the docs for the - operator you can't use it with DATETIME2 values, so if you did need ns precision (or earlier dates) you'd have an easier time expanding on the approach I suggested. And I've worked with enough databases that disagree about what DateA - DateB means that I don't like to use that syntax, but that's just my point of view... — Mark Adelsberger
– Mark Adelsberger, Commented Jan 3, 2017 at 18:45

Mark Adelsberger · Accepted Answer · 2017-01-03 15:46:48Z

4

You can call DateDiff with the date part corresponding to the smallest meaningful precision in your timestamps, and then divide to get the interval in the correct unit.

For example, if you store times down to the second, to get the number of hours call DateDiff with the date part as ss and then divide by 3600. The idea is that if your times are only accurate to the second, then "crossed 1 second boundary" means the same as "took 1 second" for all measurable purposes, and then you just convert to the units you really need.

If your intervals are long and your timestamps are high precision - e.g. if you need to use microsecond precision for year-long intervals - you may have to call DateDiff_Big instead.

UPDATE

If you need higher precision for longer intervals and can't use DateDiff_Big, then you have to get a little trickier. One way would be:

First separate the dates from the times.

Then date_diff the date parts, counting in days. (This has a range of several million years.) Multiply the result by 86,400,000 (result will need a 64-bit storage.)

Now date_diff the times with ms precision.

Note date_diff does a signed comparison, so the latter diff may be positive or negative. Add it to the part you got by multiplying the diff of the date parts, and now you have an accurate count of ms.

Divide to get the units you want.

UPDATE 2 - I originally claimed nanoseconds would work, but that's because I can't move decimal points correctly.

edited Jan 3, 2017 at 15:46

answered Jan 3, 2017 at 15:25

Mark Adelsberger

46.6k4 gold badges42 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Kalev Maricq Over a year ago

Thanks Mark. I tried doing this and ran into the issue that I don't have DateDiff_Big, since I'm on SSMS 2012.

Hart CO Over a year ago

Your sample just shows down to the second, how low do you intend to go?

Kalev Maricq Over a year ago

I think second should be fine (though it would be nice to be able to handle higher precision time stamps). The dates may be pretty far apart though (eg 100 years).

Mark Adelsberger Over a year ago

Well, you know your needs better than me, but it is strange to think that an interval of more than 24 days needs ms precision, etc. I suppose if it really does then you could do some sort of multi-stage approach. I'll see if I can work out a reasonable way

Kalev Maricq Over a year ago

Thanks Mark. I know for sure that I need days - >100 years. For units smaller than days my two concerns are 1) extensibility of the function to other uses and 2) consistent results when provided with extra precision (one time only the day might be provided, while later a timestamp with higher precision could be inserted).

|

MtwStark · Accepted Answer · 2017-01-04 09:13:38Z

0

try this,
you can add all DATEPARTS you need.

It will also work as stand alone query, no real need for function, only if you find it useful.

If you don't need nanoseconds precision, you can use datetime datatype instead of datetime2, it could be a bit easier to handle.

EDIT : Corrected boundaries behaviour

UPDATE2 : Huge syntax semplification

UPDATE3 : reverted to original syntax to avoid leap year and different months lenght problem

DROP FUNCTION FN_CALC_DATE_DIFF;
GO

CREATE FUNCTION FN_CALC_DATE_DIFF(
    @INTERVALTYPE AS INT = NULL,
    @START AS datetime,
    @END AS datetime
)
RETURNS BIGINT
AS
BEGIN

RETURN  (SELECT CASE @INTERVALTYPE    
            WHEN 0 THEN DATEDIFF(NS, DATEADD(NS, DATEDIFF(NS, 0, @START), 0), DATEADD(NS, DATEDIFF(NS, 0, @START), 0) + @Start-@End) 
            WHEN 1 THEN DATEDIFF(MCS, DATEADD(MCS, DATEDIFF(MCS, 0, @START), 0), DATEADD(MCS, DATEDIFF(MCS, 0, @START), 0) + @END-@START)
            WHEN 2 THEN DATEDIFF(MS, DATEADD(MS, DATEDIFF(MS, 0, @START), 0), DATEADD(MS, DATEDIFF(MS, 0, @START), 0) + @END-@START)
            WHEN 3 THEN DATEDIFF(SS, DATEADD(SS, DATEDIFF(SS, 0, @START), 0), DATEADD(SS, DATEDIFF(SS, 0, @START), 0) + @END-@START)
            WHEN 4 THEN DATEDIFF(MI, DATEADD(MI, DATEDIFF(MI, 0, @START), 0), DATEADD(MI, DATEDIFF(MI, 0, @START), 0) + @END-@START)
            WHEN 5 THEN DATEDIFF(HH, DATEADD(HH, DATEDIFF(HH, 0, @START), 0), DATEADD(HH, DATEDIFF(HH, 0, @START), 0) + @END-@START)
            WHEN 6 THEN DATEDIFF(DD, DATEADD(DD, DATEDIFF(DD, 0, @START), 0), DATEADD(DD, DATEDIFF(DD, 0, @START), 0) + @END-@START)
            WHEN 7 THEN DATEDIFF(WK, DATEADD(WK, DATEDIFF(WK, 0, @START), 0), DATEADD(WK, DATEDIFF(WK, 0, @START), 0) + @END-@START)
            WHEN 8 THEN DATEDIFF(MM, DATEADD(MM, DATEDIFF(MM, 0, @START), 0), DATEADD(MM, DATEDIFF(MM, 0, @START), 0) + @END-@START)
            WHEN 9 THEN DATEDIFF(YY, DATEADD(YY, DATEDIFF(YY, 0, @START), 0), DATEADD(YY, DATEDIFF(YY, 0, @START), 0) + @END-@START)
        ELSE
            DATEDIFF(SS, DATEADD(SS, DATEDIFF(SS, 0, @START), 0), DATEADD(SS, DATEDIFF(SS, 0, @START), 0) + @END-@START) -- default to
       END )
END
GO

this test

declare @d1 datetime = '31/12/2016'
declare @d2 datetime = '01/01/2017'

declare @h1 datetime = '01:31'
declare @h2 datetime = '03:20'


select 
    DBO.FN_CALC_DATE_DIFF(5, @h1, @h2) DIFF_HOURS,
    DBO.FN_CALC_DATE_DIFF(9, @d1, @d2) DIFF_YEARS

will produce

DIFF_HOURS  DIFF_YEARS
1           0

edited Jan 4, 2017 at 9:13

answered Jan 3, 2017 at 16:17

MtwStark

4,0961 gold badge23 silver badges34 bronze badges

10 Comments

Kalev Maricq Over a year ago

Thank you. I might not understand this correctly, but this seems to return boundaries passed instead of duration elapsed. If you find the difference between 12/31/2016 and 1/1/2017, does it return 1 years or 0 years?

MtwStark Over a year ago

it returns duration elapsed, it should return 0 for your dates

Kalev Maricq Over a year ago

I tried: SELECT DBO.FN_CALC_DATE_DIFF(9, '12/31/2016', '1/1/2017') DIFF_YEARS and got DIFF_YEARS = 1

Mark Adelsberger Over a year ago

@MtwStark - You seem to be basing your statement on the assumption that DATEDIFF itself would return the duration elapsed; but this is incorrect as the documentation explains.

MtwStark Over a year ago

I see, always worked wit period larger than datepart checked, but I think it could be corrected shifting back @start and @end of @start period..

|

MtwStark · Accepted Answer · 2017-01-10 14:32:42Z

Considering all the answers and comments I have finally reached a solution for this problem.

I have implemented a function to handle basic add/subtract for DATETIME2 datatype, and then I used it in a function to get intervals elapsed between two dates.

All calculations are at maximum precision of DATETIME2 and BIGINT and it should handle all cases.

this is the function to implement basic DATETIME2 arithmetic:

DROP FUNCTION FN_DATE2_MATH;
GO

CREATE FUNCTION FN_DATE2_MATH(
    @D1 AS DATETIME2(7),
    @D2 AS DATETIME2(7),
    @OP AS INT=1, -- 1 = SUM, -1 = SUBTRACT
    @OVERFLOW AS INT = NULL -- NULL = NULL VALUE, ELSE OVERFLOW ERROR
)
RETURNS DATETIME2(7)
AS
BEGIN

    IF (@OP = -1) AND (@D1>@D2) BEGIN
        DECLARE @DT DATETIME2(7) = @D1
        SET @D1 = @D2
        SET @D2 = @DT
    END

    DECLARE @B1 VARBINARY(8) = CONVERT(VARBINARY(8), REVERSE(SUBSTRING(CONVERT(VARBINARY(9), @D1),2,8)))
    DECLARE @DD1 VARBINARY(8) = SUBSTRING(CONVERT(VARBINARY(8), @B1),1,3)
    DECLARE @NS1 VARBINARY(8) = SUBSTRING(CONVERT(VARBINARY(8), @B1),4,5)

    DECLARE @B2 VARBINARY(8) = CONVERT(VARBINARY(8), REVERSE(SUBSTRING(CONVERT(VARBINARY(9), @D2),2,8)))
    DECLARE @DD2 VARBINARY(8) = SUBSTRING(CONVERT(VARBINARY(8), @B2),1,3)
    DECLARE @NS2 VARBINARY(8) = SUBSTRING(CONVERT(VARBINARY(8), @B2),4,5)

    DECLARE @DDR AS BIGINT 
    DECLARE @NSR AS BIGINT 

    IF @OP = 1 BEGIN
        SET @NSR = CONVERT(BIGINT, @NS2) + CONVERT(BIGINT, @NS1)
        IF @NSR>=864000000000 BEGIN
            SET @NSR = @NSR - CONVERT(BIGINT, 864000000000)
            SET @DD1 = CONVERT(VARBINARY(8), CONVERT(BIGINT, @DD1)-1)
        END
        SET @DDR = CONVERT(BIGINT, @DD2) + CONVERT(BIGINT, @DD1) 
    END ELSE 
        IF @OP = -1 BEGIN
            SET @NSR = CONVERT(BIGINT, @NS2) - CONVERT(BIGINT, @NS1)
            IF @NSR<0 BEGIN
                SET @NSR = @NSR + CONVERT(BIGINT, 864000000000)
                SET @DD1 = CONVERT(VARBINARY(8), CONVERT(BIGINT, @DD1)+1)
            END
            SET @DDR = CONVERT(BIGINT, @DD2) - CONVERT(BIGINT, @DD1) 
        END

    -- CHECK OVERFLOW
    IF @DDR NOT BETWEEN 0 AND 3652058 BEGIN

        IF @OVERFLOW IS NULL
            RETURN NULL
        ELSE
            RETURN DATEADD(DD, -1, CONVERT(DATETIME2(7), 0x070000000000000000)) -- GENERATE OVERFLOW            
    END

    DECLARE @BR VARBINARY(8) = CONVERT(VARBINARY(3), @DDR)+CONVERT(VARBINARY(5), @NSR)

    SET @BR = CONVERT(VARBINARY(8), REVERSE(@BR))

    RETURN  CONVERT(DATETIME2(7), 0x07+@BR)

END
GO

and this is the function to get periods elapsed:

DROP FUNCTION FN_DATE_DIFF2;
GO

CREATE FUNCTION FN_DATE_DIFF2(
    @INTERVALTYPE AS VARCHAR(11),
    @START AS DATETIME2(7),
    @END AS DATETIME2(7)
)
RETURNS BIGINT
AS
BEGIN

    DECLARE @DATEPART INT = CASE
        WHEN @INTERVALTYPE IN ('0','nanosecond','ns')   THEN 0
        WHEN @INTERVALTYPE IN ('1','microsecond','mcs') THEN 1
        WHEN @INTERVALTYPE IN ('2','millisecond','ms')  THEN 2
        WHEN @INTERVALTYPE IN ('3','second','ss','s')   THEN 3
        WHEN @INTERVALTYPE IN ('4','minute','mi','n')   THEN 4
        WHEN @INTERVALTYPE IN ('5','hour','hh')         THEN 5
        WHEN @INTERVALTYPE IN ('6','day','dd','d')      THEN 6
        WHEN @INTERVALTYPE IN ('7','week','wk','ww')    THEN 7
        WHEN @INTERVALTYPE IN ('8','month','mm','m')    THEN 8
        WHEN @INTERVALTYPE IN ('9','quarter','qq','q')  THEN 9
        WHEN @INTERVALTYPE IN ('10','year','yy','yyyy') THEN 10
    ELSE
        6 -- DEFAULT TO DAYS
    END


    DECLARE @BN0 VARBINARY(8) = 0x0000000000000000  -- 0001-01-01 00:00:00.0000000
    DECLARE @DT0 AS DATETIME2(7) = CONVERT(DATETIME2(7), 0x07+@BN0) -- datetime2(7) = 0
    --DECLARE @BNX VARBINARY(8) = 0xFFBF692AC9DAB937  -- 9999-12-31 23:59:59.9999999
    --DECLARE @DTX AS DATETIME2(7) = CONVERT(DATETIME2(7), 0x07+@BNX) -- datetime2(7) = 0

    DECLARE @DT1 AS DATETIME2(7) 
    DECLARE @DT2 AS DATETIME2(7)
    DECLARE @DP AS DATETIME2(7)

    DECLARE @VB1 VARBINARY(8) = CONVERT(VARBINARY(8), REVERSE(SUBSTRING(CONVERT(VARBINARY(9), @START),2,8)))
    DECLARE @DD1 VARBINARY(8) = SUBSTRING(CONVERT(VARBINARY(8), @VB1),1,3) -- DAYS FROM 0 TO START
    DECLARE @NS1 VARBINARY(8) = SUBSTRING(CONVERT(VARBINARY(8), @VB1),4,5) -- NS FROM 0 TO START

    DECLARE @VB2 VARBINARY(8) = CONVERT(VARBINARY(8), REVERSE(SUBSTRING(CONVERT(VARBINARY(9), @END),2,8)))
    DECLARE @DD2 VARBINARY(8) = SUBSTRING(CONVERT(VARBINARY(8), @VB2),1,3) -- DAYS FROM 0 TO END
    DECLARE @NS2 VARBINARY(8) = SUBSTRING(CONVERT(VARBINARY(8), @VB2),4,5) -- NS FROM 0 TO END

    DECLARE @NSR AS BIGINT = CONVERT(BIGINT, @NS2) - CONVERT(BIGINT, @NS1) -- NS RESULT NOT BIASED
    IF @NSR<0 BEGIN
        SET @NSR = @NSR + CONVERT(BIGINT, 864000000000) -- NS RESULT
        SET @DD1 = CONVERT(VARBINARY(8), CONVERT(BIGINT, @DD1)+1) -- ADD CARRY
    END
    DECLARE @DDR AS BIGINT = CONVERT(BIGINT, @DD2) - CONVERT(BIGINT, @DD1) -- DAYS RESULT

    DECLARE @RES BIGINT

    SET @RES = CASE @DATEPART
        WHEN 0 THEN @DDR*CONVERT(BIGINT, 864000000000)+ @NSR -- NS
        WHEN 1 THEN @DDR*CONVERT(BIGINT, 86400000000) + @NSR/CONVERT(BIGINT, 10) -- MCS
        WHEN 2 THEN @DDR*CONVERT(BIGINT, 86400000)    + @NSR/CONVERT(BIGINT, 10000)  -- MS
        WHEN 3 THEN @DDR*CONVERT(BIGINT, 86400)       + @NSR/CONVERT(BIGINT, 10000000) -- SS
        WHEN 4 THEN @DDR*CONVERT(BIGINT, 1440)        + @NSR/CONVERT(BIGINT, 600000000) -- MI
        WHEN 5 THEN @DDR*CONVERT(BIGINT, 24)          + @NSR/CONVERT(BIGINT, 36000000000) -- HH 
        WHEN 6 THEN @DDR -- DD
        WHEN 7 THEN @DDR / 7 -- WK (BOTH INT, RES = INT)
    END

    IF @DATEPART IN (8,9,10) BEGIN

        SET @DT1 = CASE @DATEPART
            WHEN 8  THEN DATEADD(MM, DATEDIFF(MM, @DT0, @START), @DT0)
            WHEN 9  THEN DATEADD(QQ, DATEDIFF(QQ, @DT0, @START), @DT0)
            WHEN 10 THEN DATEADD(YY, DATEDIFF(YY, @DT0, @START), @DT0)
        END 

        SET @DP = DBO.FN_DATE2_MATH(@START, @END, -1, 0) -- ELAPSED TIME (DIFF)
        SET @DT2 = DBO.FN_DATE2_MATH(@DT1, @DP, 1, 0) -- SHIFT DATE (ADD)

        SET @RES = CASE @DATEPART
            WHEN 8  THEN DATEDIFF(MM,  @DT1, @DT2)
            WHEN 9  THEN DATEDIFF(QQ,  @DT1, @DT2)
            WHEN 10 THEN DATEDIFF(YY,  @DT1, @DT2)
        END

    END

    RETURN @RES    
END
GO

You can call it this way:

DECLARE @D1 DATETIME2(7) 
DECLARE @D2 DATETIME2(7) 
DECLARE @DP VARCHAR(20)

SET @D1 = '31/12/2016'
SET @D2 = '01/01/2017'
SET @DP = 'YY'

SELECT @D1 DATE_START, @D2 DATE_END, @DP DATE_PART, DBO.FN_DATE_DIFF2(@DP, @D1, @D2) INTERVALS

SET @D1 = '01:31'
SET @D2 = '03:20'
SET @DP = 'HH'

SELECT @D1 DATE_START, @D2 DATE_END, @DP DATE_PART, DBO.FN_DATE_DIFF2(@DP, @D1, @D2) INTERVALS

SET @D1 = '01/01/0001'
SET @D2 = '31/12/9999 23:59:59.9999999'
SET @DP = 'NS'

SELECT @D1 DATE_START, @D2 DATE_END, @DP DATE_PART, DBO.FN_DATE_DIFF2(@DP, @D1, @D2) INTERVALS

and you will get:

DATE_START                  DATE_END                    DATE_PART   INTERVALS
2016-12-31 00:00:00.0000000 2017-01-01 00:00:00.0000000 YY          0
1900-01-01 01:31:00.0000000 1900-01-01 03:20:00.0000000 HH          1
0001-01-01 00:00:00.0000000 9999-12-31 23:59:59.9999999 NS          3155378975999999999

this should work

Collectives™ on Stack Overflow

Creating a Duration Function in SQL Server

3 Answers 3

6 Comments

10 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

10 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related