CODEWITHSUNDEEP
sqlsql-serverdatedatetime
user2461731
user2461731

Reputation: 13

How to subtract two date columns & calculate the duration in SQL Server

I have tree columns in one of the tables : check-in time and check-out time and billableHours. Check-in & check-out are date columns, 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 it returns HH:MM:SS

Question #1:

How do I subtract checkout-checkin and get billableHours in this format - HH:MM?

Now once I get HH:MM from check-in & check-out date, I would like to add HH:MM 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

Question # 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.

Upvotes: 0

Views: 5545

Answers (1)

shadowjfaith
shadowjfaith

Reputation: 942

This should work

Question #1

SELECT CONVERT(varchar(5), 
   DATEADD(minute, DATEDIFF(minute, CheckOut, CheckIn), 0), 114)

Question #2

SELECT CONVERT(varchar(5), SUM(Datediff(minute,convert(datetime,'00:00:00',108), 
  convert(datetime,BillableHours,108))), 114)
GROUP BY TicketId

Upvotes: 1

Related Questions