DateDiff() & dividing help please

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

Transact-SQL (2000)

DateDiff() & dividing help please

Author

Topic

boomboom
Starting Member

6 Posts

Posted - 2003-09-24 : 14:40:04

I have 2 date/time fields in my table. I also have a float field.

I want to get the # of hours between start_time and end_time. I want to calculate this when I update the end_time.

UPDATE timetrack
SET end_time = '09/24/2003 13:34:33 PM',
tot_hours = DATEDIFF(minute, start_time, '09/24/2003 13:34:33')/60

(start_time = '09/24/2003 08:00:00 AM')

My problem is that tot_hours is always getting set to a whole number. How can I get it to give me decimals?

If I do the update without dividing and then do another update and divide the result by 60, I get the correct results.

TIA

- Vince

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-09-24 : 14:46:48

You have to convert either denominator or the numerator to float. It has to do with order of data types. I converted the numerator below:


UPDATE timetrack
SET end_time = '09/24/2003 13:34:33 PM',
tot_hours = CONVERT(float, DATEDIFF(minute, start_time, '09/24/2003 13:34:33'))/60

Tara

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-09-25 : 07:20:56

... or you could divide by 60.0 ...

Jay White
{0}

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources