Difference between two dates in Hours

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Difference between two dates in Hours

Author

Topic

binto
Yak Posting Veteran

59 Posts

Posted - 2010-02-04 : 08:46:38

Hi all,

I have one datetime field date1 with value 2/4/2010 6:35:00 AM
and another date2 with value 2/5/2010 10:30:00 PM how can I get the result in Hours.ie the result should be date2 - date1---> 08:05.
Please help me

Thanks & Regards
Binto Thomas

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-04 : 08:49:23

[code]SELECT CONVERT(varchar(8),DATEADD(mi,DATEDIFF(mi,date1,date2),0),108) from table[/code]

binto
Yak Posting Veteran

59 Posts

Posted - 2010-02-04 : 09:01:41

sorry visakh the result should be 39:55.But your query is giving 16:00

Thanks & Regards
Binto Thomas

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-04 : 09:05:10

[code]SELECT CONVERT(varchar(2),DATEDIFF(mi,date1,date2)/60) + ':' + CONVERT(varchar(2),DATEDIFF(mi,date1,date2)%60) from table[/code]

Sachin.Nand

2937 Posts

Posted - 2010-02-04 : 09:09:55

quote:
Originally posted by visakh16

SELECT CONVERT(varchar(8),DATEADD(mi,DATEDIFF(mi,date1,date2),0),108) from table

Visakh there is an issue with your solution.Please run the following query

select CONVERT(varchar(8),DATEADD(mi,DATEDIFF(mi,'20-jan-2009 00:00:00','23-jan-2009 00:00:00'),0),108)

The query is unable to give the exact difference of 48 hours.

PBUH

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-04 : 09:12:22

quote:
Originally posted by Idera

quote:
Originally posted by visakh16

SELECT CONVERT(varchar(8),DATEADD(mi,DATEDIFF(mi,date1,date2),0),108) from table

Visakh there is an issue with your solution.Please run the following two queries

select CONVERT(varchar(8),DATEADD(mi,DATEDIFF(mi,'20-jan-2009 00:00:00','23-jan-2009 00:00:00'),0),108)

select CONVERT(varchar(8),DATEADD(mi,DATEDIFF(mi,'20-jan-2009 00:00:00','23-jan-2009 00:00:00'),0),108)

The second query is unable to give the exact difference of 48 hours.

PBUH

yeah i got it.thats because once it crosses 24 hour mrak it adds to the day part so total will skew

binto
Yak Posting Veteran

59 Posts

Posted - 2010-02-04 : 09:29:33

great post visakh.Thank you sooo much...Its working fine

Thanks & Regards
Binto Thomas

binto
Yak Posting Veteran

59 Posts

Posted - 2010-02-04 : 10:35:06

quote:

Originally posted by visakh16

SELECT CONVERT(varchar(2),DATEDIFF(mi,date1,date2)/60) + ':' + CONVERT(varchar(2),DATEDIFF(mi,date1,date2)%60) from table

visakh,

If I have

declare @date1 datetime
declare @date2 datetime

set @date1 = '2/4/2010 6:30:00'
set @date2 = '2/7/2010 06:30:00'

I am getting result as '72:0'

I want to get the result as '72:00'

And also If the hours difference overcome 99:99,I am getting wrong result.If Hour result is '132:0',I want to get 132:00.Please help me..

Thanks & Regards
Binto Thomas

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-04 : 10:46:47

[code]SELECT CONVERT(varchar(10),DATEDIFF(mi,date1,date2)/60) + ':' + RIGHT('0'+CONVERT(varchar(2),DATEDIFF(mi,date1,date2)%60),2) from table[/code]

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-02-04 : 12:03:39

[code]
select
[HH:MM] =
convert(varchar(5),datediff(hh,0,EndTime-StartTime))+
substring(convert(varchar(30),EndTime-StartTime,108),3,3),
*
from
( -- Test Data
select
StartTime = convert(datetime,'2/4/2010 6:35:00 AM'),
EndTime = convert(datetime,'2/5/2010 10:14:00 PM')
union all
select
StartTime = convert(datetime,'2/4/2010 6:35:00 AM'),
EndTime = convert(datetime,'3/12/2010 10:57:00 PM')
union all
select
StartTime = convert(datetime,'2/4/2009 6:35:00 AM'),
EndTime = convert(datetime,'2/5/2010 10:03:00 PM')
) a
[/code]

Results:
[code]
HH:MM StartTime EndTime
-------- ----------------------- -----------------------
39:39 2010-02-04 06:35:00.000 2010-02-05 22:14:00.000
880:22 2010-02-04 06:35:00.000 2010-03-12 22:57:00.000
8799:28 2009-02-04 06:35:00.000 2010-02-05 22:03:00.000

(3 row(s) affected)

[/code]

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources