Number of Hours - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

Number of Hours

Author

Topic

msastry
Starting Member

8 Posts

Posted - 2005-02-03 : 15:47:52

Could anyone advise to calculate the number of hours between two date/time objects in SQL server

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2005-02-03 : 15:48:41

Have a look at the DATEDIFF function in SQL Server Books Online

X002548
Not Just a Number

15586 Posts

Posted - 2005-02-03 : 16:07:04

Do you know what Books online is?


DECLARE @start datetime, @end datetime

SELECT @start = '1/1/2005 00:00:00', @end = '12/31/2005 23:59:59'

SELECT DATEDIFF(hh,@start,@end)

Brett

8-)

msastry
Starting Member

8 Posts

Posted - 2005-02-03 : 16:29:05

I thought about using the datediff funtion but I always thought that it would only retun whole hours.

e.g datediff('h',4:00am,5.50am)

would return 1 hour whereas I am trying to get in to return the decimal 1.50

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2005-02-03 : 16:37:59

How about something like:


DECLARE @start datetime, @end datetime

SELECT @start = '1/1/2005 00:00:00', @end = '12/31/2005 23:59:59'
SELECT DATEDIFF(mi,@start,@end)/60 + cast((DATEDIFF(mi,@start,@end) - (DATEDIFF(mi,@start,@end)/60 * 60)) as numeric(10,2))/100

msastry
Starting Member

8 Posts

Posted - 2005-02-03 : 17:38:44

Solved the issue by using:

SELECT convert(decimal(12,2),DATEDIFF(m, startdate, getdate()) / 12.00) AS no_of_days

anyway! Thanks for your support.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources