CODEWITHSUNDEEP
sqlsql-server-2012
ThaiThai
ThaiThai

Reputation: 127

Calculate time difference in two different dates SQL

I have two column - start_time and end_time in my sql database. Below is my data

start_time: 2019-09-17 06:00:00.000 
end_time: 2019-09-18 06:00:00.000

When i run this query:

select CONVERT(varchar(5),(end_time-start_time),108) from activity

it returns 00:00

I need the output to be 24:00. How can i achieve this?

Upvotes: 0

Views: 139

Answers (3)

Ed Bangga
Ed Bangga

Reputation: 13026

select case will do.

select case when Format(end_time-start_time, 'HH:mm', 'en-US') = '00:00' then '24:00' else 
    Format(end_time-start_time, 'HH:mm', 'en-US') end from activity

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522719

The output format you want is non standard, because it isn't really time (e.g. the hours component could exceed 24). One option would be to use DATEDIFF in minutes mode and then tease out the hours and minutes components:

SELECT
    CONVERT(VARCHAR(10), DATEDIFF(minute, '2019-09-17 06:00:00', '2019-09-19 06:13:00') / 60) +
    ':' +
    CONVERT(VARCHAR(10), DATEDIFF(minute, '2019-09-17 06:00:00', '2019-09-19 06:13:00') % 60) AS hours

This outputs: 48:13

Demo

Upvotes: 3

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32001

try with datediff function

select datediff(hh,'2019-09-17 06:00:00.000','2019-09-18 06:00:00.000')

so in your case it would be 

select datediff(hh,start_time,end_time) from activity

Upvotes: 1

Related Questions