CODEWITHSUNDEEP
sql-server-2005
abdul
abdul

Reputation: 1

Time duration between two dates

i need to get the time duration in (hh:mm:ss) format between the two dates

2011/05/05 11:45:02 and 2011/05/01 08:09:57

For example if I had these two dates 2011/05/05 01:18:14 and 2011/05/05 11:00:00, the result would be: 02:18:14

Upvotes: 0

Views: 3556

Answers (3)

Abdul rasheed.A
Abdul rasheed.A

Reputation: 31

DECLARE @dt1 datetime

DECLARE @dt2 datetime

SELECT @dt1 = '2011-05-05 11:45:02', @dt2 = '2011-05-05 08:09:57'

SELECT CONVERT(VARCHAR(8),@dt1-@dt2,108)


-- RESULT IS : 03:35:05

Upvotes: 3

Dalex
Dalex

Reputation: 3625

Try this:

declare @date1 datetime='2011/05/05 01:18:14', @date2 datetime='2011/05/05 11:00:00'
select CAST((@date2-@date1) as time(0))

Here is important order of elements in statement.In other case you will get 24h-your time.

Upvotes: 0

il_guru
il_guru

Reputation: 8508

As far as i know there is no DATETIME_INTERVAL Data type in SQL (or TSQL) , so the only way you have to accomplish this is to manually format the result of a DATEDIFF function.

 declare @hours as int
 declare @minutes as int
 declare @seconds as int
 declare @time_interval as nvarchar(10)

 set @hours =  DATEDIFF(ss,'2011/05/05 01:18:14', '2011/05/05 11:00:00') / 3600
 set @minutes = (DATEDIFF(ss,'2011/05/05 01:18:14', '2011/05/05 11:00:00') - @hours*3600)/60
 set @seconds = DATEDIFF(ss,'2011/05/05 01:18:14', '2011/05/05 11:00:00') - @hours*3600 - @minutes * 60

 set @time_interval = (cast(@hours as nvarchar) +':'+ cast(@minutes as nvarchar)+':'+ cast(@seconds as nvarchar))

print @time_interval

Upvotes: 0

Related Questions