Get time difference in the format HH:MM:SS for two different DateTime values in SQL Server

Introduction

We are trying to get the time (HH:MM:SS) between Task Started Date and End Date (for example, office timings used to start at 7:30 AM and end at 6:30 PM). If we want to extract a particular time for a particular task just send START DATE and END DATE of task to this procedure. It will give the exact time. Another important point is we need to remove weekends (Saturday and Sunday). Here I am considering only weekdays, which means only working days.

Using the code

Here I am dividing the solution into two conditions: Task finished on same date and Task finished on different dates.  

Check the following procedure and send two DateTime values to these procedures and they will return the exact time (HH:MM:SS).

USE [DATABASENAME]
GO
/****** Object:  StoredProcedure [dbo].[GETTIME]    Script Date: 08/19/2013 16:16:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[GETTIME](@STARTDATE Datetime,
      @ENDDATE Datetime,@result varchar(500) OUTPUT)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
BEGIN TRY
    -- Insert statements for procedure here
	DECLARE @DayNameCheck varchar(250),@daynum1 int,@daynum2 int,@diffHours int,@dayDifday int;
	
	SELECT @daynum1=(SELECT datepart(WEEKDAY,@STARTDATE));
	SELECT @daynum2=(SELECT datepart(WEEKDAY,@ENDDATE));
	
	SELECT @diffHours=(SELECT DATEDIFF(HH, @STARTDATE,@ENDDATE));
	
	SELECT @dayDifday=(SELECT DATEDIFF(day,@STARTDATE,@ENDDATE));
	
	--If Both Dates Are Same
	
	IF((SELECT(CONVERT(DATE,@STARTDATE)))= (SELECT(CONVERT(DATE,@ENDDATE))))
	BEGIN	
	DECLARE @getsecond int;
	SELECT @getsecond=DATEDIFF(SECOND,@STARTDATE, @ENDDATE);
	--SELECT @result=(Convert(numeric(18,3),
	--   (SELECT DATEDIFF(SECOND,@STARTDATE, @ENDDATE)))/Convert(numeric(18,3),3600));	
	
	--Declare @SubtractDate1 as datetime
	----Enter Number of Seconds here	
	--Set @SubtractDate1=(SELECT DateAdd(s,@getsecond,getdate()) - Getdate())  
	--SELECT @result=(Select (Convert(varchar(10),DatePart(hh,@SubtractDate1))+ ' : ' +
	--  Convert(varchar(10),DatePart(mi,@SubtractDate1))+ ' : ' +
	--     Convert(varchar(10),DatePart(ss,@SubtractDate1))));
	  SELECT @result=(SELECT CAST(@getsecond/3600 AS VARCHAR(10))+ 
	         RIGHT(CONVERT(CHAR(8),DATEADD(ss,@getsecond,0),108),6));
	END
		
    --If Both Dates Are Different
		
	ELSE IF((SELECT(CONVERT(DATE,@STARTDATE)))!=(SELECT(CONVERT(DATE,@ENDDATE))))
	BEGIN	
	DECLARE @Date DATE,@gg DATE,@cc Date,@val int=0,@getseconds int=0,
	@getstart Time,@EndTime Time='18:30:00.000',@StartTime Time='07:30:00.000',@getweek int;
	
	
	SELECT @Date=(SELECT Convert(DATE,@STARTDATE));
    SELECT @getstart=(SELECT Convert(TIME,@STARTDATE));
	SELECT @getseconds=@getseconds+DATEDIFF(SECOND,(CAST (@Date AS DATETIME) + 
	       @getstart),(CAST (@Date AS DATETIME) + @EndTime))
	
	WHILE(@val<@dayDifday)
	BEGIN	
	SELECT @Date=(SELECT Convert(DATE,DATEADD(day,1,@Date)));--Adding one day to Date
	SELECT @getweek=(SELECT datepart(WEEKDAY,@Date));	
	IF(@getweek!=1 AND @getweek!=7)
	BEGIN
	
	IF(@Date!=Convert(DATE,@ENDDATE))
	BEGIN	
	SELECT @getseconds=@getseconds+DATEDIFF(SECOND,(CAST (@Date AS DATETIME) + 
	       @StartTime),(CAST (@Date AS DATETIME) + @EndTime))
	END        
	ELSE IF(@Date=Convert(DATE,@ENDDATE))
	BEGIN
	DECLARE @tt Time;
	SELECT @tt=CONVERT(Time,@ENDDATE)
	
	SELECT @getseconds=@getseconds+DATEDIFF(SECOND,(CAST 
	  (Convert(DATE,@ENDDATE) AS DATETIME) + @StartTime),
	  (CAST (Convert(DATE,@ENDDATE) AS DATETIME) + @tt))
	BREAK;
	END	
	END
	SET @val=@val+1;	
	END
	
	--SELECT @result=(Convert(numeric(18,3),@getseconds)/Convert(numeric(18,3),3600));		
	

 SELECT @result=(SELECT CAST(@getseconds/3600 AS VARCHAR(10))+ 
          RIGHT(CONVERT(CHAR(8),DATEADD(ss,@getseconds,0),108),6));
 --     	Declare @SubtractDate as datetime
	----Enter Number of Seconds here	
	--Set @SubtractDate=(SELECT DateAdd(s,@getseconds,getdate()) - Getdate())
--	SELECT @result=(Select Convert(varchar(10),DateDiff(day,'1900-01-01',@SubtractDate))
--+ ' Day(s) '+(Convert(varchar(10),DatePart(hh,@SubtractDate))+ ' : ' + 
--      Convert(varchar(10),DatePart(mi,@SubtractDate))+ ' : ' +
--        Convert(varchar(10),DatePart(ss,@SubtractDate))));
	END
	end try
	
	begin catch
	--select @Resultflag=0;
	DECLARE @ErrorNumber    INT            = ERROR_NUMBER()
DECLARE @ErrorMessage   NVARCHAR(4000) = ERROR_MESSAGE()
DECLARE @ErrorProcedure NVARCHAR(4000) = ERROR_PROCEDURE()
DECLARE @ErrorLine      INT            = ERROR_LINE()
RAISERROR ('An error occurred within a user transaction.
Error Number        : %d
Error Message       : %s 
Affected Procedure  : %s
Affected Line Number: %d', 16, 1, @ErrorNumber, @ErrorMessage,@ErrorProcedure,@ErrorLine)

end catch
	
END