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





5.00/5 (1 vote)
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