Calculate time elapsed between Dates

# Calculate time elapsed between Dates

• Wednesday, February 13, 2013 9:56 PM

I'm working on a query to calculate the time elapsed between dates stored in a table.  I would like to write the query without using a cursor(pretty big tables with many columns).  I have a table that stores employees and a table that stores the time an employee clicks a button in a certain section of a web application.  The employee table is linked to the process table by employee id.  There are multiple process rows for each employee.  I will have to use the process id value in the process table to determine what section the button was clicked in.  The process date values will be used for the elapsed time calculation.  Once I have a date for each section, I will need to calculate the elapsed time(in days, hours, minutes) between certain sections.  This query will be used in a report that runs weekly.

ex.  processdate(Section 1)              processdate(Section 2)                                   Time elapsed 1-2

2009-09-28 13:09:49.360       2010-01-22 14:42:09.510           time elapsed in days, hours, minutes?

I have a few questions?

1.  What is the best approach? Do I need cursor to loop thru all of the dates for each employee.  I'm worried about the impact on performance?

2.  How to caculate time elapsed in days, hours, minutes?

3. Should I use temp tables to get all of the sections in one row like the example above before doing the time calculation?

I've listed the relevant table columns below:

CREATE TABLE [dbo].[Employee](
[Empid] [int] NOT NULL,
[First_Name] [varchar](50) NULL,
[Last_Name] [varchar](50) NULL,
[Hire_Date] [datetime] NULL,
....
)
CREATE TABLE [dbo].[Process](

[Empid] [int] NULL,
[processid] [int] null,
[processDate] [datetime] NULL,

......
)

• Edited by Wednesday, February 13, 2013 9:57 PM
•

### All Replies

• Wednesday, February 13, 2013 10:20 PM

Hi James_P

Pérez

Update: 02/19/2013 I can't believe how badly I messed up in this thread. We only need to get the difference once between dates. The seconds is the best choice. It will not only do the difference for the seconds but it gives you the difference in seconds. So we didn't have to do it for each cycle aka year, month, day, hour, minute, second... :( my bad!
• Edited by Tuesday, February 19, 2013 10:11 PM
• Edited by Tuesday, February 19, 2013 11:07 PM
•
• Wednesday, February 13, 2013 11:20 PM
Moderator

The following blog is on the same topic:

http://www.sqlusa.com/bestpractices2005/employment/

Do you really need minutes?

Kalman Toth Database & OLAP Architect
Paperback / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

• Thursday, February 14, 2013 6:15 AM

you can try this script -

--CREATE TABLE [dbo].[Employee](
--	[Empid] [int] NOT NULL,
--	[First_Name] [varchar](50) NULL,
--	[Last_Name] [varchar](50) NULL,
--	[Hire_Date] [datetime] NULL
--	)
--CREATE TABLE [dbo].[Process](

--	[Empid] [int] NULL,
--	[Processid] [int] null,
--	[ProcessDate] [datetime] NULL
--	)
--INSERT Employee (EmpID,First_Name,Last_Name,Hire_Date) VALUES (1,'a','b', GETDATE()-200)
--INSERT Employee (EmpID,First_Name,Last_Name,Hire_Date) VALUES (2,'c','d', GETDATE()-150)
--INSERT Employee (EmpID,First_Name,Last_Name,Hire_Date) VALUES (3,'e','f' ,GETDATE()-170)
--INSERT Employee (EmpID,First_Name,Last_Name,Hire_Date) VALUES (4,'g','h', GETDATE()-100)
--INSERT Process(Empid,Processid,ProcessDate) VALUES (1,1,GETDATE()-200)
--INSERT Process(Empid,Processid,ProcessDate) VALUES (1,2,GETDATE()-190)
--INSERT Process(Empid,Processid,ProcessDate) VALUES (1,3,GETDATE()-183)
--INSERT Process(Empid,Processid,ProcessDate) VALUES (1,4,GETDATE()-150)
--INSERT Process(Empid,Processid,ProcessDate) VALUES (2,1,GETDATE()-150)
--INSERT Process(Empid,Processid,ProcessDate) VALUES (2,5,GETDATE()-130)
--INSERT Process(Empid,Processid,ProcessDate) VALUES (2,3,GETDATE()-120)
--INSERT Process(Empid,Processid,ProcessDate) VALUES (3,1,GETDATE()-170)
--INSERT Process(Empid,Processid,ProcessDate) VALUES (3,4,GETDATE()-100)
--INSERT Process(Empid,Processid,ProcessDate) VALUES (3,7,GETDATE()-50)
--INSERT Process(Empid,Processid,ProcessDate) VALUES (4,1,GETDATE()-100)
WITH CTE (RN, EmpID, First_Name, Last_Name, Hire_Date,ProcessID, ProcessDate)
AS
(SELECT ROW_NUMBER() OVER (PARTITION BY E.empID ORDER BY E.EmpID, P.ProcessDate) AS RN
,E.EmpID
,E.First_Name
,E.Last_Name
,E.Hire_Date
,P.ProcessID
,P.ProcessDate
FROM Employee E
JOIN Process P
ON E.Empid = P.Empid
)
SELECT
CASE B.ProcessDate WHEN NULL THEN 0
ELSE DATEDIFF(DAY,A.ProcessDate,B.ProcessDate)
END AS [Days]
,CASE B.ProcessDate WHEN NULL THEN 0
ELSE DATEDIFF(HOUR,A.ProcessDate,B.ProcessDate)%24
END AS [Hours]
,CASE B.ProcessDate WHEN NULL THEN 0
ELSE DATEDIFF(MINUTE,A.ProcessDate,B.ProcessDate)%60
END AS [Minutes]
,A.*
,B.*
FROM CTE A
LEFT JOIN CTE B
ON A.RN+1 = B.RN
AND A.EmpID = B.EmpID
ORDER BY A.EmpID,A.RN

• Tuesday, February 19, 2013 7:30 PM

Hi Perez,

I'm trying to incorporate the code that was used in your thread referenced above.  I have around 50 process date columns in one table that I need to calculate the elapsed time on.  If I understand your script correctly, I would have to repeat the script each time I needed to calculate the elapsed time between two dates.  Is there anyway I can do this in one pass thru?  Should I create a function to do this?

I've added the first two columns to your script below.  Each date column has a unique name.

DECLARE @TableWithStuff table (Client int,processdate1Dt datetime,processdate2Dt datetime)
INSERT INTO @TableWithStuff (Client,processdate1Dt,processdate2Dt) VALUES
(1,'2012-04-03','2012-04-06 11:58')
--+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-- Script
--+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Select * from @TableWithStuff
;with AbstractingTheBaseDataSet as (
SELECT
Client
/* Removed so that group by will work ;)
,AuditProcessedDt
*/
,[Years] = SUM(ABS(DATEPART(YEAR,processdate1Dt)-DATEPART(YEAR,processdate2Dt)))
,[Months] = SUM(ABS(DATEPART(MONTH,processdate1Dt)-DATEPART(MONTH,processdate2Dt)))
,[Days] = SUM(ABS(DATEPART(DAY,processdate1Dt)-DATEPART(DAY,processdate2Dt)))
,[HOURS] = SUM(ABS(DATEPART(HH,processdate1Dt)-DATEPART(HH,processdate2Dt)))
,[Minutes] = SUM(ABS(DATEPART(MM,processdate1Dt)-DATEPART(MM,processdate2Dt)))
,[Seconds] = SUM(ABS(DATEPART(SS,processdate1Dt)-DATEPART(SS,processdate2Dt)))
FROM @TableWithStuff
GROUP BY Client)
SELECT --* FROM AbstractingTheBaseDataSet
CONVERT(VARCHAR(255),(DATEPART(YEAR,CalculationDate)-DATEPART(YEAR,'19000101'))) + 'y'
+ CONVERT(VARCHAR(255),(DATEPART(MONTH,CalculationDate)-DATEPART(MONTH,'19000101'))) + 'm'
+ CONVERT(VARCHAR(255),(DATEPART(DAY,CalculationDate)-DATEPART(DAY,'19000101'))) + 'd '
+ CONVERT(VARCHAR(255),(DATEPART(HH,CalculationDate)-DATEPART(HH,'19000101'))) + ':'
+ CONVERT(VARCHAR(255),(DATEPART(MM,CalculationDate)-DATEPART(MM,'19000101'))) + ':'
+ CONVERT(VARCHAR(255),(DATEPART(SS,CalculationDate)-DATEPART(SS,'19000101')))
FROM AbstractingTheBaseDataSet

• Edited by Tuesday, February 19, 2013 7:31 PM
•
• Tuesday, February 19, 2013 9:57 PM

Hi James_P

Long Answer: Try this. Warning Not tested thoroughly!

---- table with many date columns
--IF OBJECT_ID(N'dbo.mytime',N'U') IS NOT NULL DROP TABLE dbo.mytime
--CREATE TABLE dbo.mytime (
--LogID BIGINT IDENTITY(1,1),
--ProcessDate1 DATETIME,
--ProcessDate2 DATETIME,
--ProcessDate3 DATETIME,
--ProcessDate4 DATETIME,
--ProcessDate5 DATETIME,
--ProcessDate6 DATETIME,
--ProcessDate7 DATETIME,
--ProcessDate8 DATETIME,
--ProcessDate9 DATETIME,
--ProcessDate10 DATETIME,
--CONSTRAINT pk_LogID PRIMARY KEY (LogID)
--)
--INSERT INTO dbo.mytime (
--ProcessDate1,
--ProcessDate2,
--ProcessDate3,
--ProcessDate4,
--ProcessDate5,
--ProcessDate6,
--ProcessDate7,
--ProcessDate8,
--ProcessDate9,
--ProcessDate10
--)-- Control Group 1 Day apart
--),-- Test Group Randomly set apart
--)
--GO
--ALTER FUNCTION dbo.fn_TimeDifference (@date1 DATETIME, @date2 DATETIME)
--CREATE FUNCTION dbo.fn_TimeDifference (@date1 DATETIME, @date2 DATETIME)
--RETURNS VARCHAR(30)
--WITH EXECUTE AS CALLER
--AS
--BEGIN
--	DECLARE @DtDiffString VARCHAR(30)
--	;WITH cte_DateMath AS (
--	)
--	SELECT @DtDiffString =
--	  CONVERT(VARCHAR(3),(DATEPART(YEAR,DtDiff)-DATEPART(YEAR,'19000101'))) + 'y'
--	+ CONVERT(VARCHAR(3),(DATEPART(MONTH,DtDiff)-DATEPART(MONTH,'19000101'))) + 'm'
--	+ CONVERT(VARCHAR(3),(DATEPART(DAY,DtDiff)-DATEPART(DAY,'19000101'))) + 'd '
--	+ CONVERT(VARCHAR(3),(DATEPART(HH,DtDiff)-DATEPART(HH,'19000101'))) + ':'
--	+ CONVERT(VARCHAR(3),(DATEPART(MM,DtDiff)-DATEPART(MM,'19000101'))) + ':'
--	+ CONVERT(VARCHAR(3),(DATEPART(SS,DtDiff)-DATEPART(SS,'19000101')))
--	FROM cte_DateMath
--	RETURN @DtDiffString
--END
--GO

SELECT
dbo.fn_TimeDifference(ProcessDate1, ProcessDate2)
,dbo.fn_TimeDifference(ProcessDate3, ProcessDate4)
,dbo.fn_TimeDifference(ProcessDate5, ProcessDate6)
,dbo.fn_TimeDifference(ProcessDate7, ProcessDate8)
,dbo.fn_TimeDifference(ProcessDate9, ProcessDate10)
FROM dbo.mytime

Pérez

• Wednesday, February 27, 2013 1:50 PM

I understand that the ABS function returns the absolute value of a number.  This would prevent negative numbers from being displayed.  Would the function work if I removed ABS?  I will need to identify and report to the users when the numbers are negative.  This would mean that the input dates are invalid.  Is there any way to change the function logic to return "Invalid" if the ABS value is negative?

• Edited by Wednesday, February 27, 2013 8:24 PM
• Edited by Thursday, February 28, 2013 2:26 PM
•