October 28, 2009 at 8:32 am
Hi All
Suppose i provide 8 and 11 and 15 as inputs to stored procedure. First two values denote hour number like 8am and 11am and last input number means 15 minutes. I want a stored procedure or script which accepts these numbers and gives me output as
8:15 ,8:30,8:45,9:00,9:15,9:30,9:45,10:00,10:15,10:30,10:45,11:00
Regards,
Dhruv
October 28, 2009 at 10:39 am
This sounds a lot like a school project. Why in the world would you need something like this otherwise? That being what it is this was a fun challenge!!
Here is what I came up with:
declare @StartHour int
declare @EndHour int
declare @Interval int
set @StartHour = 8
set @EndHour = 11
set @Interval = 15
declare @Date datetime
declare @ReturnVal varchar(2000)
set @ReturnVal = ''
declare @NumReturnVals int
select @NumReturnVals = ((@EndHour * 60) - (@StartHour * 60)) / @Interval
set @Date = '1/1/1900 ' + right('0' + cast(@StartHour as varchar(2)), 2) + ':00:00.000'
declare @CurrentReturnNum int
set @CurrentReturnNum = 0
while(@CurrentReturnNum < @NumReturnVals) begin
set @Date = dateadd(n, @Interval, @Date)
set @ReturnVal = @ReturnVal + ', ' + cast(datepart(hh, @Date) as varchar(2)) + ':' + right('0' + cast(datepart(n, @Date) as varchar(2)), 2)
set @CurrentReturnNum = @CurrentReturnNum + 1
end
select right(@ReturnVal, datalength(@ReturnVal) - 2)
I'm sure there are some easier and more efficient ways to accomplish but this runs really fast. It also makes some assumptions that the startdate < enddate, the interval will provide at least one result. It is however flexible enough to allow for a big time span (within a single day) and the interval can be any size as long as it will generate at least one required timespan. 😉 If this is a real world thing then I would add some validation before using this.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 28, 2009 at 3:00 pm
12 or 24 hour clock?
October 28, 2009 at 3:06 pm
24.
For example i just ran it with start = 19 and end =22
output was:
19:15, 19:30, 19:45, 20:00, 20:15, 20:30, 20:45, 21:00, 21:15, 21:30, 21:45, 22:00
Of course some conditional divide by 12 logic could be added to get the output in 12 hour format. 😛
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 28, 2009 at 8:02 pm
slange-862761 (10/28/2009)
This sounds a lot like a school project. Why in the world would you need something like this otherwise? That being what it is this was a fun challenge!!Here is what I came up with:
A "fun" challenge would be to do it without a loop or a function... care to give it a shot? I'll give you a hint... "Tally" table.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 2, 2009 at 12:23 am
Hi Slange,
Thank you very much for posting a quick reply. I am weak in date and time calculation kind of thing. I required this for a project which i am making on my own for commercial purpose.This is a online appointment with doctor for which i wanted to display time gaps in which a patient can choose to take appointment from doctor.
Thank you very much once again....
Dhruv
November 2, 2009 at 1:35 am
Might i also suggest you dont want the output as
8:15 ,8:30,8:45,9:00,9:15,9:30,9:45,10:00,10:15,10:30,10:45,11:00
More like
8:15
8:30
8:45
9:00
........
10:45
11:00
As at some point you will need to join this table back to an 'existing appointments' table.
November 2, 2009 at 2:28 am
Hi Mays,
Yes you are right , i will be requiring time output in columns so that like 8:15 amd 8: 30 are shown as seperate columns and the solution provided by Slange can be altered accordingly to show time gaps as columns.I have an appointment table in which i will be storing actual appointments.I dont need to store all the time gaps.When the patient checks some available time , only that will be stored in table.Ofcourse , i will have to make union of actual time values stored in table and the time gaps i am getting from Slange's script.
Dhruv
November 2, 2009 at 2:40 am
As Jeff Says a tally table will be the best way,
Simply Add (TallyNum *15) minutes to your starting time.
Give it a try and let us know how you get on.
November 3, 2009 at 8:53 am
Here's a big clue:
SELECT DATEADD(minute,(N-1)*@deltaMins,DATEADD(hh,@startHour,0))
FROM Tally
I've intentionally omitted a where clause as an exercise for the OP.
See the link in my sig for Jeff's tally table article.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply