NuRoN Consulting, INC.

Blog > SQL Tools > Databases > Util > Functions > dbo.GetDateTimeDifference

SQL scalar function: Util.dbo.GetDateTimeDifference

CREATE FUNCTION dbo.GetDateTimeDifference (@StartDateTime DATETIME,
                                          @EndDateTime DATETIME)
RETURNS VARCHAR(128)
AS
BEGIN
    RETURN

    (SELECT  ISNULL(CAST(NULLIF(DATEDIFF(HOUR, @StartDateTime, @EndDateTime) / 24, 0) AS VARCHAR) + ' days ', '') + RIGHT('0'
                                                                                                                     + CAST(DATEDIFF(MINUTE, StartDateTime,
                                                                                                                                     @EndDateTime) / 60 % 24 AS VARCHAR),
                                                                                                                     2) + ':' + RIGHT('0'
                                                                                                                                      + CAST(DATEDIFF(MINUTE,
                                                                                                                                                StartDateTime,
                                                                                                                                                @EndDateTime)
                                                                                                                                       % 60 AS VARCHAR), 2)
        + ':' + RIGHT('0' + CAST(DATEDIFF(SECOND, StartDateTime, @EndDateTime) % 60 AS VARCHAR), 2)
FROM    (SELECT DATEDIFF (DAY, @StartDateTime, @EndDateTime)AS DayDiff) dd
CROSS APPLY (SELECT CASE WHEN DayDiff > 1 THEN DATEADD(DAY, DayDiff - 1, @StartDateTime)
                         ELSE @StartDateTime
                    END AS StartDateTime) b)
END

Description for SQL scalar function: Util.dbo.GetDateTimeDifference

Todo