Rounding Time up to the nearest custom interval

[foreveryoung]

I am trying to work out a function that will allow us to round a given time to the nearest custom interval.

I work in a hospital where we have consultant clinics. Take the example of a typical clinic where the session runs from 09:00 to 12:00 with each appointment lasting 30mins.

So to fit there must be an appointments starting at the following times

09:00
09:30
10:00
10:30
11:00
11:30

The problem is that within our data we have appointments for example at 10:05 and we want that time to say 10:30 instead which is the next appointment time after 10:05.

I have come up with a script that converts the times into seconds past midnight and does some rounding but then I get a bit stuck.

declare @starttime as int
declare @slottime as int
declare @appttime as int
declare @newtime as int

set @starttime = (cast(substring('12:05',1,2) as int) * 3600) + (cast(substring('12:05',4,2) as int) * 60)

set @appttime = (cast(substring('12:06',1,2) as int) * 3600) + (cast(substring('12:06',4,2) as int) * 60)

set @slottime = 5

set @newtime = @starttime + round(@starttime/@appttime,0) + @slottime

print @newtime



select 3600 * 6

print 43505 / 3600

print ((cast(43505 as decimal(10,0))/cast(3600 as decimal(10,0)))-round((43505/3600),0)) * 60


Can anyone suggest a script that will help me here?

David

 

[Catadmin]

Check out the CEILING function in Books Online. With a little bit of converting and math, you should be able to use it to get your 1/2 hour mark.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"