Round to nearest quarter hour 2

[antiskeptic]

I have some really strange date fields in my current database. It is a number, counting the minutes since 1/1/1930. So my code to extract the date and time is as follows:

substring(convert( varchar, dateadd( mi, process_raw.start_time,
convert( datetime, '01/01/1930' ) ), 100 ), 13,6) as[starttime],
substring(convert( varchar, dateadd( mi, process_raw.stop_time,
convert( datetime, '01/01/1930' ) ), 100 ), 13,6) as[stoptime]

Then I have a formula that calculates the time between the two:

round((process_raw.stop_time-process_raw.start_time)/60.0,2)[ActualTimeHours]

But I want this [ActualTimeHours] to be rounded to the nearest 15 minutes. So I can use it in the next step of this craziness. (-:
Briefly... the next step entails getting the difference between an extra 2 hours ([Remainder]) and the [ActualTimeHours].

Example:

if [ActualTimeHours] = 1.25 then [Remainder] = .75
or
if [ActualTimeHours] = 1.0 then [Remainder] = 1.0
etc...


If I haven't confused you quite thouroughly, please help point me in the right direction. I've been pondering this problem for a couple days and keep coming up empty. And help would be greatly appreciated!!

Thank you,
AntiSkeptic

 

[vongrunt]

This rounds number to nearest quarter:

select round ( value*4, 0 )/4

 

[TheJon]

Make sure your minutes are converted to numeric and not an integer!

compare:

select round(87.0/60*4,0)/4

with:

select round(87/60*4,0)/4

[tt]|_ |_ _ | _ _
|, | )(/, |(_)| )
'[/tt]

 

[antiskeptic]

Thanks a million to both of you...
that did the trick!

And Jon, you were right about the avoiding the integer thing, that really helped.

Now, I know this is probably a dumb question, and I'll probably kick myself when I see how to do it...but does anyone have an idea as to the best way to go about the second part of my dilemma. The difference of [ActualTimeHours] and [Remainder] to equal 2 hours?
Or did I even make any sence with that part of it?

Thanks again for your help TheJon & Vongrunt!!!

Sincerely,
Antiskeptic

 

[TheJon]

[Remainder] = 2.0 - [actual time hours]?

I don't know what you may want to do when your remainder goes negative though!

[tt]|_ |_ _ | _ _
|, | )(/, |(_)| )
'[/tt]

 

[antiskeptic]

right! I think I can work with that, as Negative happens on very rare ocasions with these records.

I was thinking that it was a little more complicated than that. Guess I was over thinking it. Sorry...*sheepish grin*...I KNEW I'd kick myself.

However...you'll never belive this, but they just threw a whole new set of rules at me...a few minutes ago.
Now they want to see the [ActualTimeHours] in hour:minute
format, as opposed to the 1.5 hours. AND they don't want to round to the 15 minutes now. *ARGH* (But at least I learned that for future reference. *ha*)
So now I have to change :
((process_raw.stop_time-process_raw.start_time)/60.0) [ActualTimeHours]

from an output of 1.5 to 1:30

do you happen to know how to do that?

Thanks again for all your help!

Sincerely,
AntiSkeptic

 

[TheJon]

Try:

select convert(varchar(5), dateadd(mi, ([ActualTimeHours] * 60), 0), 8)

It creates a datetime of 1/1/1900 + [ActualTimeHours] (1/1/1900 is serial time 0), then displays the first five characters of the time portion.

[tt]|_ |_ _ | _ _
|, | )(/, |(_)| )
'[/tt]

 

[ESquared]

Hey antiskeptic, what if the two times are more than 24 hours apart, or if the second time is earlier in a day than the first?

TheJon's method will work only for up to 24 hours. 25 hours will be displayed as 1 hour.

If the start time is 23:59 and the end time is 00:01 the next day, won't that be confusing? You need to calculate starttime and stoptime without truncating the date.

To display a number of minutes in the format hh:mm, accomodating values over 24 hours, try this:

select duration = convert(varchar(2),(stop_time - start_time) / 60) + ':' + convert(varchar(2),(stop_time - start_time) % 60)

Note: I am assuming that start_time and stop_time are integers. If they are not you'll have to do some converting and such.

-------------------------------------
A sacrifice is harder when no one knows you've made it.