calculate hh and mm between 2 dates

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

SQL Server 2005 Forums

Transact-SQL (2005)

calculate hh and mm between 2 dates

Author

Topic

doran_doran
Posting Yak Master

179 Posts

Posted - 2009-06-19 : 10:46:36

Any snippet on how to get hh and mm between two dates? Minute must be in 100th. for example, minute "30" should be "50". The final result would look like 4.50 (4 hours and 30 minutes)

Any help or link or snippet or suggession will be truly appreciated.

Thanks
Do

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-06-19 : 10:55:07

declare @date1 datetime
declare @date2 datetime

set @date2 = getdate()
set @date1 = dateadd(ss,-8524,@date2)

select @date1,@date2
,[DifferenceInMinutes] = DATEDIFF(mi,@date1,@date2)
,[HoursMinutes] = ROUND(DATEDIFF(mi,@date1,@date2) / 60.0,2)

Jim

doran_doran
Posting Yak Master

179 Posts

Posted - 2009-06-19 : 11:05:36

PERFECT. Except I have 4 after decimal, just need 2

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-06-19 : 11:15:48

That's a formatting issue and should be handled in the front-end, however

select @date1,@date2
,[DifferenceInMinutes] = DATEDIFF(mi,@date1,@date2)
,[HoursMinutes] = ROUND(CONVERT(Numeric(5,2),DATEDIFF(mi,@date1,@date2) / 60.0),2)

Jim

doran_doran
Posting Yak Master

179 Posts

Posted - 2009-06-19 : 11:17:38

cast(ROUND(DATEDIFF(mi,@date1,@date2) / 60.0,2) as decimal(18,2))

this fixed it. I dont know if this was the preferred method .

doran_doran
Posting Yak Master

179 Posts

Posted - 2009-06-19 : 11:33:16

Hi Jim,
Sorry, we posted at the same time. Yours look good too.

THANK YOU VERY VERY MUCH FOR BEING PROMPT, HELPFULL AND YOUR VALUABLE TIME.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources