Rounding to the nearest quarter hour ?

Question

Rounding to the nearest quarter hour ?

jbalbo

SSCertifiable

Points: 7583
More actions
August 12, 2015 at 8:08 am

#303276

Hi
I am getting the time difference between two dates using
DATEDIFF(second,Information.[Start Time],Information.[End Time]) / 60.00 / 60.00 AS hours,
My output looks like
1.33
0.17
1.50
etc
I'd like to round to the nearest quarter hour
1.50
0.25
.150
etc
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

Do you always want to round up? Or why isn't the first result 1.25?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

jbalbo SSCertifiable Points: 7583 More actions · Answer 2

Yes sorry , bad example, I am looking to round up to the nearest 1/4 hour

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

An idea:

SELECT DATEDIFF(second,Information.[Start Time],Information.[End Time]) / 60.00 / 60.00,

CEILING( DATEDIFF(second,Information.[Start Time],Information.[End Time])*4 / 60.00 / 60.00) / 4 AS Rounded_Hours,

ROUND( DATEDIFF(second,Information.[Start Time],Information.[End Time])*4 / 60.00 / 60.00, 0) / 4 AS Rounded_Up_Hours,

ROUND( DATEDIFF(second,Information.[Start Time],Information.[End Time])*4 / 60.00 / 60.00, 0, 1) / 4 AS Rounded_Down_Hours

FROM (VALUES( CAST( '05:30' as time), CAST( '06:50' as time)),

( CAST( '07:20' as time), CAST( '07:30:20' as time)),

( CAST( '12:00' as time), CAST( '13:30' as time)) ) Information([Start Time],[End Time])

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

jbalbo SSCertifiable Points: 7583 More actions · Answer 4

CEILING( DATEDIFF(second,Information.[Start Time],Information.[End Time])*4 / 60.00 / 60.00) / 4 AS Rounded_Hours,

Works as I needed , and I now have other options in my libray

Thanks so much!!