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
 Rounding on time

Author  Topic 

nevzab
Starting Member

34 Posts

Posted - 2013-06-19 : 10:48:27
I am trying to convert minutes into hours as follows...

Minutes | Desired Result
13 0.0
60 1.0
90 1.5
98 1.5
200 3.25
230 3.75


Cheers!

Nev.


James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-19 : 10:53:36
[code](Minutes/15*15)/60.0[/code]
Go to Top of Page

nevzab
Starting Member

34 Posts

Posted - 2013-06-19 : 11:00:05
Nice one James. I must return to school to take maths again!!

Cheers.
Go to Top of Page

nevzab
Starting Member

34 Posts

Posted - 2013-06-19 : 11:12:55
If I did want .25 in the event that the minutes were less than 15 would I have to use a CASE statement?

select CASE WHEN (DATEDIFF(MINUTE,on_site,Off_Site)/15*15) = 0
THEN 0.25 else
(DATEDIFF(MINUTE,on_site,Off_Site)/15*15) / 60.0
END







from call_events
Go to Top of Page

mmkrishna1919
Yak Posting Veteran

95 Posts

Posted - 2013-06-19 : 12:05:34
Hi nevzab,
If Minute value is 45 then are u expecting 0.0 or 0.750000?

Thanks.

M.MURALI kRISHNA
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-19 : 14:19:18
quote:
Originally posted by nevzab

If I did want .25 in the event that the minutes were less than 15 would I have to use a CASE statement?

select CASE WHEN (DATEDIFF(MINUTE,on_site,Off_Site)/15*15) = 0
THEN 0.25 else
(DATEDIFF(MINUTE,on_site,Off_Site)/15*15) / 60.0
END







from call_events

If you want to round up only for 0 to 15 minutes, then you would need a special case like you added.
Go to Top of Page

nevzab
Starting Member

34 Posts

Posted - 2013-06-19 : 17:50:17
quote:
Originally posted by mmkrishna1919

Hi nevzab,
If Minute value is 45 then are u expecting 0.0 or 0.750000?

Thanks.

M.MURALI kRISHNA



I would expect 0.75. Anything <= 15 minutes = 0.25 otherwise round down to the nearest 15 minutes (0.25)
Go to Top of Page
   

- Advertisement -