DATEDIFF in Hours / Minutes - 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 2005 Forums

Transact-SQL (2005)

DATEDIFF in Hours / Minutes

Author

Topic

Jonny1409
Posting Yak Master

133 Posts

Posted - 2007-09-07 : 06:51:15

Hello,

I have a really quick syntax question please as I'm really new to SQL (as you can probably tell by the basic questions)

I am using Datediff to get the number of hours between two times :

datediff(hh, StartTime, EndTime) as length

However, this only shows whole hours as opposed to say 1 and a half hours between 12.00 and 1.30

Therefore I thought I'd use minutes and divide the result by 60.
This again, gives me the whole hours but not the minutes.

I've tried using CAST as float but I must be doing it wrong.

What would be the syntax for this please ?

Kristen
Test

22859 Posts

Posted - 2007-09-07 : 06:54:29

"I've tried using CAST as float but I must be doing it wrong."

If you do

SELECT 11 / 2

the result will be an integer, because both values are integers. That doesn;t change if you do

SELECT CONVERT(float, 11 / 2)

because the inner bit is still integer maths, whereas

SELECT CONVERT(float, 11) / CONVERT(float, 2)

will force a floating point division

EDIT: As KHTan points out below, if you have CONSTANT values you can force "floating point" by including a decimal, so:

SELECT 11 / 2 -- uses integer maths
SELECT 11.0 / 2.0 -- uses floating point maths

Kristen