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:

1. 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.

2. DateDiff takes a datepart argument, which has resisted all my attempts to pass as a variable, except via dynamic sql.

3. 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.

4. (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:

1. A non-dynamic way to pass dateparts as variables

2. A way to run dynamic SQL from a function

3. 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

Answer 1

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

Answer 2

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

Answer 3

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.