SQL Date/Time

How to subtract two dates and get the time difference?

The answer is in this UDF. It takes as input two parameters:

1- StartDate

2- EndDate

It will return the time difference in this format: (-) HH:MM

CREATE FUNCTION [dbo].[TimeDiff](@date1 DATETIME, @date2 DATETIME)
-- Returns a string of (-) hh:mm
RETURNS VARCHAR(7)
AS
    BEGIN
        -- Check if the two dates are equal
        IF (@date1 = @date2)
            RETURN ('0:0')
 
        -- Fix both dates to have same date keeping same time
        DECLARE @DayDiff INT
        DECLARE @MonthDiff INT
        DECLARE @YearDiff INT
 
        SET @DayDiff        = DATEDIFF(dd,@date1,@date2)
        SET @date1         = DATEADD(dd,@DayDiff,@date1)        
        SET @MonthDiff    = DATEDIFF(mm,@date1,@date2)
        SET @date1         = DATEADD(mm,@MonthDiff,@date1)        
        SET @YearDiff        = DATEDIFF(yyyy,@date1,@date2)
        SET @date1         = DATEADD(yyyy,@YearDiff,@date1)        
            
        -- Get Hours and Minutes    
        DECLARE @Hours INT
        DECLARE @Minutes INT
        SELECT @Minutes = DATEDIFF(mi,@date1,@date2)
        SELECT @Hours   = (@Minutes - @Minutes/(24*60))/60
        SELECT @Minutes = @Minutes - @Hours*60
 
        DECLARE @returnTime VARCHAR(7)
        DECLARE @SignFlag BIT
        SELECT @SignFlag = 0
 
        IF ( (@Hours <0) OR (@Minutes<0) )
        BEGIN
            SELECT @SignFlag = 1
            If (@Hours <0)
                SELECT @Hours = @Hours * (-1)
            ELSE
                SELECT @Minutes = @Minutes * (-1)
        END
 
        IF ( (@Hours < 10) OR (@Hours = 0) )
            SELECT @returnTime = '0' + CAST(@Hours AS VARCHAR(2))
        ELSE
            SELECT @returnTime = CAST(@Hours AS VARCHAR(2))
    
        SELECT @returnTime = @returnTime + ':'
 
        IF ( (@Minutes < 10) OR (@Minutes = 0) )
            SELECT @returnTime = @returnTime + '0' + CAST(@Minutes AS VARCHAR(2))
        ELSE
            SELECT @returnTime = @returnTime + CAST(@Minutes AS VARCHAR(2))
 
        IF (@SignFlag = 1)
            SELECT @returnTime = '- ' + @returnTime
    
        RETURN @returnTime
    END
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: