36

I am facing some difficulty with calculating the time difference between two dates.

What I want is, I have two dates let say 

@StartDate = '10/01/2012 08:40:18.000'
@EndDate='10/04/2012 09:52:48.000'

so the difference between two dates in the form of hh:mm:ss is 72:42:30.

How can I get this result in a T-SQL query?

sashoalm
  • 75,001
  • 122
  • 434
  • 781
Gnanasekaran Kuppusamy
  • 669
  • 2
  • 8
  • 12
  • 1
    possible duplicate of [Calculating timespan with t-sql](http://stackoverflow.com/questions/758891/calculating-timespan-with-t-sql) – Pfitz Nov 27 '12 at 07:48

15 Answers15

62
declare @StartDate datetime, @EndDate datetime

select @StartDate = '10/01/2012 08:40:18.000',@EndDate='10/04/2012 09:52:48.000'

select convert(varchar(5),DateDiff(s, @startDate, @EndDate)/3600)+':'+convert(varchar(5),DateDiff(s, @startDate, @EndDate)%3600/60)+':'+convert(varchar(5),(DateDiff(s, @startDate, @EndDate)%60)) as [hh:mm:ss]

This query will helpful to you.

Himanshu
  • 31,810
  • 31
  • 111
  • 133
Franklin
  • 895
  • 1
  • 11
  • 19
  • How would you pad the minutes and seconds? – callisto Jul 11 '19 at 11:43
  • @callisto You can add formatting around the converts, but will have to treat them as INT instead. select format(convert(int, datediff(s, startDate, endDate)/3600), 'd2')+':'+format(convert(int, datediff(s, startDate, endDate)%3600/60), 'd2')+':'+format(convert(int, datediff(s, startDate, endDate)%60), 'd2;) as [hh:mm:ss] – Spazmoose Aug 21 '19 at 15:10
  • @Spazmoose : See my answer below for another way without casting to int. – callisto Aug 22 '19 at 07:45
42

The shortest code would be: 

Select CAST((@EndDateTime-@StartDateTime) as time(0)) '[hh:mm:ss]'
Jack
  • 10,943
  • 13
  • 50
  • 65
SQLnbe
  • 848
  • 9
  • 11
9

While maybe not the most efficient, this would work:

declare @StartDate datetime, @EndDate datetime

select @StartDate = '10/01/2012 08:40:18.000',@EndDate='10/04/2012 09:52:48.000'

select convert(varchar(5),DateDiff(s, @startDate, @EndDate)/3600)+':'+convert(varchar(5),DateDiff(s, @startDate, @EndDate)%3600/60)+':'+convert(varchar(5),(DateDiff(s, @startDate, @EndDate)%60))

if you can run two selects then this would be better because you only do the datediff once:

declare @StartDate datetime, @EndDate datetime

select @StartDate = '10/01/2012 08:40:18.000',@EndDate='10/04/2012 09:52:48.000'
declare @Sec BIGINT

select @Sec = DateDiff(s, @startDate, @EndDate)

select convert(varchar(5),@sec/3600)+':'+convert(varchar(5),@sec%3600/60)+':'+convert(varchar(5),(@sec%60))
Greg
  • 3,442
  • 3
  • 29
  • 50
8

I like the idea of making this into a function so it becomes re-useable and your queries become much easier to read:

--get the difference between two datetimes in the format: 'h:m:s'
CREATE FUNCTION getDateDiff(@startDate DATETIME, @endDate DATETIME)
RETURNS VARCHAR(10)
AS BEGIN
    DECLARE @seconds INT = DATEDIFF(s, @startDate, @endDate)
    DECLARE @difference VARCHAR(10) =
    CONVERT(VARCHAR(4), @seconds / 3600) + ':' +
    CONVERT(VARCHAR(2), @seconds % 3600 / 60) + ':' +
    CONVERT(VARCHAR(2), @seconds % 60)
    RETURN @difference
END

Usage:

DECLARE @StartDate DATETIME = '10/01/2012 08:40:18.000'
DECLARE @endDate DATETIME = '10/04/2012 09:52:48.000'

SELECT dbo.getDateDiff(@startDate, @endDate) AS DateDifference

Result:

    DateDifference
1   73:12:30

It's also easier to read the result if you add padding so the format is always hh:mm:ss. For example, here's how you would do that in SQL Server 2012 or later:

--get the difference between two datetimes in the format: 'hh:mm:ss'
CREATE FUNCTION getDateDiff(@startDate DATETIME, @endDate DATETIME)
RETURNS VARCHAR(10)
AS BEGIN
    DECLARE @seconds INT = DATEDIFF(s, @startDate, @endDate)
    DECLARE @difference VARCHAR(10) =
    FORMAT(@seconds / 3600, '00') + ':' +
    FORMAT(@seconds % 3600 / 60, '00') + ':' +
    FORMAT(@seconds % 60, '00')
    RETURN @difference
END

Note that this will not clip the hour if it is more than 2 digits long. So 1 hour would show up as 01:00:00 and 100 hours would show up as 100:00:00

Elaskanator
  • 1,135
  • 10
  • 28
MarredCheese
  • 17,541
  • 8
  • 92
  • 91
  • 1
    This is my favorite answer because of reusability and formatting (both the code and the result padding). And thanks for making sure it handles when the duration is >= 100 hours – Elaskanator Jul 12 '18 at 20:24
5
DECLARE @dt1 datetime='2012/06/13 08:11:12', @dt2 datetime='2012/06/12 02:11:12'

SELECT CAST((@dt2-@dt1) as time(0))
Rafael
  • 7,002
  • 5
  • 43
  • 52
Kash
  • 61
  • 1
  • 1
  • Within a single select statement, I have one column returning a time in the future, a second column is getdate() and a third column uses your logic replacing dt2 and dt1 with the definition of the future date and getdate() respectively. Return values are 2017-04-12 21:00:00.000 2017-04-11 10:14:20.590 13:14:21 By my maths there should be about 24+11 hours, that is, 35 hours between them, not 13. – youcantryreachingme Apr 11 '17 at 00:22
4

If you're not opposed to implicit type casting I'll offer this an alternative solution. Is it more readable with better formatting? You be the judge.

DECLARE  @StartDate datetime = '10/01/2012 08:40:18.000'
        ,@EndDate   datetime = '10/04/2012 09:52:48.000'

SELECT
    STR(ss/3600, 5) + ':' + RIGHT('0' + LTRIM(ss%3600/60), 2) + ':' + RIGHT('0' + LTRIM(ss%60), 2) AS [hh:mm:ss]
FROM (VALUES(DATEDIFF(s, @StartDate, @EndDate))) seconds (ss)
guest
  • 41
  • 1
4

If you need a zero padded difference between 2 dates:

SELECT convert(varchar(2),FORMAT(DATEDIFF(s, @startDate, @endDate)/3600,'0#'))+':'    
   +convert(varchar(2),FORMAT(DATEDIFF(s, @startDate, @endDate)%3600/60,'0#'))+':'
   +convert(varchar(2),FORMAT(DATEDIFF(s, @startDate, @endDate)%60,'0#')) AS Duration
callisto
  • 4,921
  • 11
  • 51
  • 92
1

Try this one:

declare @StartDate datetime, @EndDate datetime

select @StartDate = '2016-05-04 10:23:41.083', @EndDate='2016-05-04 10:25:26.053'

select CAST(DateDiff(MI, @startDate, @EndDate)/60 AS varchar)+':'+Cast(DateDiff(MI, @startDate, @EndDate)%60 AS varchar)+':'+cast(DateDiff(s, @startDate, @EndDate)%60 AS varchar) as [hh:mm:ss]
Prashant Pimpale
  • 10,349
  • 9
  • 44
  • 84
Roshni Bokade
  • 343
  • 2
  • 9
0

Take a look at these. I didn't use more parenthesis to keep it readable, so remember that multiplication is done before addition or subtraction.

Both below return:

hr  mins  sec   timediff
73   12    30   73:12:30

This is written to not use a sub-query and be the most readable and understandable: 

declare @StartDate datetime,
@EndDate datetime

set @StartDate = '10/01/2012 08:40:18.000'
set @EndDate =   '10/04/2012 09:52:48.000' 

select datediff(hour, @StartDate, @EndDate) hr,
   datediff(minute, @StartDate, @EndDate) 
   - datediff(hour, @StartDate, @EndDate) * 60 mins,
   datediff(second, @StartDate, @EndDate) 
   - (datediff(minute, @StartDate, @EndDate) * 60) sec,
   cast(datediff(hour, @StartDate, @EndDate) as varchar)+':'+ 
   cast(datediff(minute, @StartDate, @EndDate) 
   - datediff(hour, @StartDate, @EndDate) * 60 as varchar)+':'+ 
   cast(datediff(second, @StartDate, @EndDate) 
   - (datediff(minute, @StartDate, @EndDate) * 60) as varchar) timediff

This is a version that would perform better if you have a lot of data. It requires a sub-query.

declare @StartDate datetime,
@EndDate datetime

set @StartDate = '10/01/2012 08:40:18.000'
set @EndDate =   '10/04/2012 09:52:48.000' 

select s.seconds / 3600  hrs,
s.seconds / 60 - (seconds / 3600 ) * 60 mins,
s.seconds - (s.seconds / 60) * 60   seconds,
cast(s.seconds / 3600 as varchar) + ':' +
cast((s.seconds / 60 - (seconds / 3600 ) * 60) as varchar) + ':' +
cast((s.seconds - (s.seconds / 60) * 60) as varchar) timediff
from (select datediff(second, @StartDate, @EndDate) as seconds) s
JBrooks
  • 9,901
  • 2
  • 28
  • 32
0

I came across this post today as I was trying to gather the time difference between fields located in separate tables joined together on a key field. This is the working code for such an endeavor. (tested in sql 2010) Bare in mind that my original query co-joined 6 tables on a common keyfield, in the code below I have removed the other tables as to not cause any confusion for the reader.

The purpose of the query is to calculate the difference between the variables CreatedUTC & BackupUTC, where difference is expressed in days and the field is called 'DaysActive.'

declare @CreatedUTC datetime
declare @BackupUtc datetime


SELECT TOP 500

table02.Column_CreatedUTC AS DeviceCreated,
CAST(DATEDIFF(day, table02.Column_CreatedUTC, table03.Column_EndDateUTC) AS nvarchar(5))+ ' Days' As DaysActive,
table03.Column_EndDateUTC AS LastCompleteBackup

FROM

Operations.table01 AS table01

LEFT OUTER JOIN

    dbo.table02 AS table02
ON
    table02.Column_KeyField = table01.Column_KeyField

LEFT OUTER JOIN 

    dbo.table03 AS table03
ON
    table01.Column_KeyField = table03.Column_KeyField

Where table03.Column_EndDateUTC > dateadd(hour, -24, getutcdate()) --Gathers records with an end date in the last 24 hours
AND table02.[Column_CreatedUTC] = COALESCE(@CreatedUTC, table02.[Column_CreatedUTC])
AND table03.[Column_EndDateUTC] = COALESCE(@BackupUTC, table03.[Column_EndDateUTC])

GROUP BY table03.Column_EndDateUTC, table02.Column_CreatedUTC
ORDER BY table02.Column_CreatedUTC ASC, DaysActive, table03.Column_EndDateUTC DESC

The Output will be as follows:

[DeviceCreated]..[DaysActive]..[LastCompleteBackup]
---------------------------------------------------------
[2/13/12 16:04]..[463 Days]....[5/21/13 12:14]
[2/12/13 22:37]..[97 Days].....[5/20/13 22:10]
chris
  • 1
  • 1
0
declare @StartDate datetime,
@EndDate datetime
set @StartDate = '10/01/2012 08:40:18.000'
set @EndDate =   '10/01/2012 09:52:48.000'

SELECT CONVERT(CHAR(8), CAST(CONVERT(varchar(23),@EndDate,121) AS DATETIME)
-CAST(CONVERT(varchar(23),@StartDate,121)AS DATETIME),8) AS TimeDiff
xpt
  • 20,363
  • 37
  • 127
  • 216
DPayne
  • 17
  • 1
0

It's A Script Write Copy then write in your script file and change your requered field and get out put

DECLARE @Sdate DATETIME, @Edate DATETIME, @Timediff VARCHAR(100)
SELECT @Sdate = '02/12/2014 08:40:18.000',@Edate='02/13/2014 09:52:48.000'
SET @Timediff=DATEDIFF(s, @Sdate, @Edate)
SELECT CONVERT(VARCHAR(5),@Timediff/3600)+':'+convert(varchar(5),@Timediff%3600/60)+':'+convert(varchar(5),@Timediff%60) AS TimeDiff
Baby Groot
  • 4,637
  • 39
  • 52
  • 71
Naimish Mungara
  • 213
  • 1
  • 4
  • 14
0
DECLARE  @StartDate datetime = '10/01/2012 08:40:18.000'
        ,@EndDate   datetime = '10/10/2012 09:52:48.000'
        ,@DaysDifferent int = 0
        ,@Sec BIGINT

select @Sec = DateDiff(s, @StartDate, @EndDate)

IF (DATEDIFF(day, @StartDate, @EndDate) > 0)
    BEGIN
        select @DaysDifferent = DATEDIFF(day, @StartDate, @EndDate)
        select @Sec = @Sec - ( @DaysDifferent * 86400 )
        SELECT LTRIM(STR(@DaysDifferent,3)) +'d '+ LTRIM(STR(@Sec/3600, 5)) + ':' + RIGHT('0' + LTRIM(@Sec%3600/60), 2) + ':' + RIGHT('0' + LTRIM(@Sec%60), 2) AS [dd hh:mm:ss]
    END
ELSE
    BEGIN
        SELECT LTRIM(STR(@DaysDifferent,3)) +'d '+ LTRIM(STR(@Sec/3600, 5)) + ':' + RIGHT('0' + LTRIM(@Sec%3600/60), 2) + ':' + RIGHT('0' + LTRIM(@Sec%60), 2) AS [dd hh:mm:ss]
    END

----------------------------------------------------------------------------------
dd HH:MM:SS
9d 1:12:30
user3464593
  • 1
0
declare @StartDate datetime;
declare @EndDate datetime;
select @StartDate = '10/01/2012 08:40:18.000';
select @EndDate='10/04/2012 09:52:48.000';
select  cast(datediff(hour,@StartDate,@EndDate) as varchar(10)) + left(right(cast(cast(cast((@EndDate-@StartDate) as datetime) as time) as varchar(16)),14),6)
Pang
  • 9,564
  • 146
  • 81
  • 122
Hong
  • 11
0

Here you go. The DATEDIFF is only called once - which makes a difference in large queries. No CONVERT, but 4 FORMATS. Not sure if that makes a difference. Each part is optional. You can easily remove the optional code to slim it down. I suppose you could even write separate functions and remove the optional code for whatever you need, link

dbo.GetElapsedTimeHM

to return only hh:mm

alter function dbo.GetElapsedTime(@Start datetime, @End datetime, 
        @d bit = 0, @h bit = 1, @m bit = 1, @s bit = 1)
returns varchar(11) as 

begin

/*
declare @Start datetime, @End datetime, @d bit = 1, @h bit = 1, @m bit = 1, @s bit = 1
set @Start = '2023-05-24 16:34:22.900'
set @End = '2023-05-26 13:57:43.070'
select dbo.GetElapsedTime(@Start, @End, 1,1,1,1)
select dbo.GetElapsedTime(@Start, @End, @d, @h, @m, @s)
*/

declare @result varchar(11)

;with totalsecs as (select DATEDIFF(s, @Start, @End) as NumOfSecs 
), DayNum as (select (NumOfSecs / 86400) * 86400 as DaySecs from totalsecs
), hrs as (select ((NumOfSecs - DaySecs) / 3600) * 3600 as HrSecs from DayNum, totalsecs
), Mins as (select  ((NumOfSecs - DaySecs - HrSecs) / 60) * 60 as MinSecs from hrs, DayNum, totalsecs
), secs as (select NumOfSecs - DaySecs - HrSecs - MinSecs as SecSecs from mins, hrs, DayNum, totalsecs
) 
select @result = 
    case @d when 1 then RIGHT('0' + FORMAT(DaySecs / 86400, ''), 2) else '' end
    + case when @d = 1 and @h = 1 then ':' else '' end 
    + case @h when 1 then RIGHT('0' + FORMAT(HrSecs / 3600, ''), 2) else '' end
    + case when (@d = 1 or @h = 1) and @m = 1 then ':' else '' end
    + case @m when 1 then RIGHT('0' + FORMAT(MinSecs / 60, ''), 2) else '' end
    + case when (@d = 1 or @h = 1 or @m = 1) and @s = 1 then ':' else '' end
    + case @s when 1 then RIGHT('0' + FORMAT(SecSecs, ''), 2) else '' end
from secs, mins, hrs, DayNum, totalsecs

return @result
--select @result
end
FirstByte
  • 563
  • 1
  • 6
  • 20