how to subtract two date columns & calculate the duration in MSSQL

Question

• I have tree columns in one of the tables: check-in time and check-out time, billableHours. check-in & check-out are date fields. billableHours is varchar and would like to format like this: HH:MM.

I could use following query

SELECT CONVERT(VARCHAR,(Checkout-checkIn),108) from WorkTrackingLog

but I get HH:MM:SS

Question # 1: How do I subtract checkout-checkin and get HH:MM?

Now once I get HH:Mm from check-in & check-out date, I would like to add these numbers and get the totalDuration in other table.

For example lets say I have three records:

Table # 1:

------------------------

TicketId |  billableHours

------------------------

1001    |           05:04

1001    |           12:19

1001    |           02:16

------------------------

Table # 2

------------------------

TicketId | totalDuration

------------------------

1001    |           19:39

------------------------

Questions # 2 - How do I convert HH:Mm to int and add multiple records together to get totalDuration. Total duration can be varchar format.

Make sense?

thank you in advance for your input/feedback.

Friday, December 20, 2013 8:08 PM

Answers

• I suggest to stick with the date arithmetic instead of making conversions to varchar.

E.g.

select TicketId, sum(datediff(minute, checkin, checkout)) as Duration

from Tickets GROUP BY TicketID

You can always format the duration back to hh:mm doing simple date math.

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

My TechNet articles

Friday, December 20, 2013 8:19 PM
• First question is easy.  Try:

`SELECT CONVERT(VARCHAR(5),(Checkout-checkIn),108) from WorkTrackingLog `

Second is easy as well:

```Select
cast(left(billableHours, 2) as int) * 60
+ cast(right(billableHours, 2) as int) as billableMinutes from WorkTrackingLog```

To sum up and put into hours:

```Select
TicketId,
substring(cast(
10000 +
sum(cast(left(billableHours, 2) as int) * 60
+ cast(right(billableHours, 2) as int))
as varchar(5) )
, 2,2)
+ ':'
+ right(cast(sum(cast(left(billableHours, 2) as int) * 60
+ cast(right(billableHours, 2) as int))
as varchar(5)) , 2)

as totalHours
From WorkingTrackingLoggroup by TicketID```

Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com

Friday, December 20, 2013 8:29 PM
• `Question # 1: How do I subtract checkout-checkin and get HH:MM? `

Now once I get HH:Mm from check-in & check-out date, I would like to add these numbers and get the totalDuration in other table.

For example lets say I have three records:

Table # 1:

------------------------

TicketId |  billableHours

------------------------

1001    |           05:04

1001    |           12:19

1001    |           02:16

------------------------

You can get an idea from this how calculate the time difference and reprsent it in hh:mm format

```DECLARE @T TABLE (TICKET_ID INT,
CHECK_IN_TIME DATETIME,
CHECK_OUT_TIME DATETIME)

INSERT @T VALUES (1001,'2013-10-09 23:34:59.345','2013-10-10 01:23:45.456'),
(1001,'2013-10-11 02:23:34.345','2013-10-11 04:45:23.345'),
(1001,'2013-10-12 18:23:34.456','2013-10-12 20:34:32.678')

SELECT * ,DATEDIFF(SECOND,CHECK_IN_TIME,CHECK_OUT_TIME)AS SECS
,CAST(CAST(DATEADD(SECOND, DATEDIFF(SECOND,CHECK_IN_TIME,CHECK_OUT_TIME),'00:00:00')AS TIME(0))AS VARCHAR(5))
FROM @T```

Questions # 2 - How do I convert HH:Mm to int and add multiple records together to get totalDuration. Total duration can be varchar format.

```SELECT TICKET_ID,CAST( CAST(DATEADD(SECOND,SUM(DATEDIFF(SECOND,CHECK_IN_TIME,CHECK_OUT_TIME)),'00:00:00')AS TIME(0))  AS VARCHAR(5)) AS TOTAL_SECS

FROM @T
GROUP BY TICKET_ID```

Thanks, hsbal

Friday, December 20, 2013 8:54 PM

All replies

• I suggest to stick with the date arithmetic instead of making conversions to varchar.

E.g.

select TicketId, sum(datediff(minute, checkin, checkout)) as Duration

from Tickets GROUP BY TicketID

You can always format the duration back to hh:mm doing simple date math.

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

My TechNet articles

Friday, December 20, 2013 8:19 PM
• First question is easy.  Try:

`SELECT CONVERT(VARCHAR(5),(Checkout-checkIn),108) from WorkTrackingLog `

Second is easy as well:

```Select
cast(left(billableHours, 2) as int) * 60
+ cast(right(billableHours, 2) as int) as billableMinutes from WorkTrackingLog```

To sum up and put into hours:

```Select
TicketId,
substring(cast(
10000 +
sum(cast(left(billableHours, 2) as int) * 60
+ cast(right(billableHours, 2) as int))
as varchar(5) )
, 2,2)
+ ':'
+ right(cast(sum(cast(left(billableHours, 2) as int) * 60
+ cast(right(billableHours, 2) as int))
as varchar(5)) , 2)

as totalHours
From WorkingTrackingLoggroup by TicketID```

Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com

Friday, December 20, 2013 8:29 PM
• `Question # 1: How do I subtract checkout-checkin and get HH:MM? `

Now once I get HH:Mm from check-in & check-out date, I would like to add these numbers and get the totalDuration in other table.

For example lets say I have three records:

Table # 1:

------------------------

TicketId |  billableHours

------------------------

1001    |           05:04

1001    |           12:19

1001    |           02:16

------------------------

You can get an idea from this how calculate the time difference and reprsent it in hh:mm format

```DECLARE @T TABLE (TICKET_ID INT,
CHECK_IN_TIME DATETIME,
CHECK_OUT_TIME DATETIME)

INSERT @T VALUES (1001,'2013-10-09 23:34:59.345','2013-10-10 01:23:45.456'),
(1001,'2013-10-11 02:23:34.345','2013-10-11 04:45:23.345'),
(1001,'2013-10-12 18:23:34.456','2013-10-12 20:34:32.678')

SELECT * ,DATEDIFF(SECOND,CHECK_IN_TIME,CHECK_OUT_TIME)AS SECS
,CAST(CAST(DATEADD(SECOND, DATEDIFF(SECOND,CHECK_IN_TIME,CHECK_OUT_TIME),'00:00:00')AS TIME(0))AS VARCHAR(5))
FROM @T```

Questions # 2 - How do I convert HH:Mm to int and add multiple records together to get totalDuration. Total duration can be varchar format.

```SELECT TICKET_ID,CAST( CAST(DATEADD(SECOND,SUM(DATEDIFF(SECOND,CHECK_IN_TIME,CHECK_OUT_TIME)),'00:00:00')AS TIME(0))  AS VARCHAR(5)) AS TOTAL_SECS

FROM @T
GROUP BY TICKET_ID```

Thanks, hsbal

Friday, December 20, 2013 8:54 PM
• thank you Russ:

This is perfect.

Ronak

Friday, December 20, 2013 9:11 PM
• Thank Harry for you input.
Friday, December 20, 2013 9:11 PM
• thank you Naomi. Appreciate your input.
Friday, December 20, 2013 9:13 PM
• >> I have three columns in one of the tables: check-in time and check-out time, billableHours. <<

Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

This is minimal polite behavior on SQL forums. Now we have to guess at everything and do your typing. Hfre is mine:

CREATE TABLE Timecards
(ticket_nbr CHAR(12) NOT NULL,
check_in_timestamp DATETIME2(0) NOT NULL
check_out_timestamp DATETIME2(0), --- null is on-going
CHECK (check_in_timestamp <= check_out_timestamp),
PRIMARY KEY (ticket_nbr, check_in_timestamp));

>> check-in & check-out are date fields [sic: columns are not fields]. billable_hours is VARCHAR and would like to format like this: HH:MM. <<

You might, but an SQL programmer would not. What is the first principle of any tiered architecture? Display formatting is done in the presentation layers, and never in the database. This is more than SQL. The term "field" in ANSI/ISO Standard  SQL refers to the year, month, day, hour minute and seconds in a temporal data type. And the only unit of time in the metric system is the second.

Billable time  is a quantity being measured in minutes (my assumption), so it is numeric. If you want to present it as decimal hours (the usual way for billings) or Babylonian Base sixty times, do not do it here. Oh, we do not use the old 1970's Sybase CONVERT string function; we have temporal math now.

But we do not have your computational rules. Do you want to trim the timestamps to the nearest minute in the columns or after they are subtracted? I would go with rounding up

SELECT ticket_nbr,
CEILING (
SUM(DATEDIFF(SECOND, check_in_timestamp,
check_out_timestamp))/ 60))
AS billable_minutes
FROM Timecards
GROUP ticket_nbr;

>> I would like to add these numbers and get the billable_hours_tot in other table. <<

Again an SQL programmer would put this in a VIEW, so that any corrections are current. Unlike punch cards and mag tapes, we do not have to materialize our data.

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

Sunday, December 22, 2013 11:58 PM