Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Rouding time values up to the nearest custon interval
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Ask SQLTeam Question

0 Posts

Posted - 11/29/2006 :  08:07:25  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
David Mercer writes "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


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?


Patron Saint of Lost Yaks

30421 Posts

Posted - 11/29/2006 :  08:51:33  Show Profile  Visit SwePeso's Homepage  Reply with Quote
-- prepare test data
declare	@test table (timeinfo datetime)

insert	@test
select	'10:05' union all
select	'11:17' union all
select	'12:30' union all
select	'09:15' union all
select	'10:00' union all
select	'10:59'

declare @intervals table (minutes tinyint)

insert	@intervals
select	5 union all
select	10 union all
select	15 union all
select	30 union all
select	45 union all
select	60 union all
select	90

-- do the work
select		t.timeinfo,
		i.minutes interval,
		DATEADD(minute, (i.minutes - datediff(minute, 0, t.timeinfo) % i.minutes) % i.minutes, t.timeinfo) [nearest future time]
from		@test t
cross join	@intervals i
order by	i.minutes,

Peter Larsson
Helsingborg, Sweden

Edited by - SwePeso on 11/29/2006 09:04:01
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000