• Luis Cazares - Thursday, December 28, 2017 10:37 AM

    Didn't we gave some faster alternatives in the forums for this function?

    I don't if there are any published, but this one is an MTVF, so we know it'll be handicapped right out of the gate.

    Here's one I wrote some time ago that performs pretty well.  If anyone sees any ways to speed it up, I'm very open to them.

    ALTER FUNCTION dbo.DateDiffDHMS(@StartDate Datetime,
               @EndDate Datetime) RETURNS TABLE WITH SCHEMABINDING
    AS

    RETURN (
      WITH cteParts AS (
      SELECT Days  = DATEDIFF(second, @StartDate, @EndDate) / 86400,
         Hours = (DATEDIFF(second, @StartDate, @EndDate) / 3600) - (DATEDIFF(second, @StartDate, @EndDate) / 86400 * 24),
         Minutes = (DATEDIFF(second, @StartDate, @EndDate) / 60) - (DATEDIFF(second, @StartDate, @EndDate) / 3600 * 60),
         Seconds = DATEDIFF(second, @StartDate, @EndDate) % 60
      )
      SELECT Days, Hours, Minutes, Seconds,
       DHMS = CONVERT(Varchar(8), Days) + ':' +
         RIGHT('00' + CONVERT(Varchar(2), Hours), 2) + ':' +
         RIGHT('00' + CONVERT(Varchar(2), Minutes), 2) + ':' +
         RIGHT('00' + CONVERT(Varchar(2), Seconds), 2)
      FROM cteParts
    );

    It does a heap of 100K rows in 120 ms and a heap of 1M rows in 1023 ms.

    One limitation is that, because the base difference is in seconds, the max difference will be capped at 24,855 days, or just over 68 years.  That's where it runs into the maximum value of an integer.  It won't be good for everything, but it's good for everything we use it for.RDA SQL Server Documentation and Trainin  For greater differences, it could be adapted to use a larger resolution for the base difference.  I figure a resolution of 1 second over 68 years was good enough for how we use it.