SQL Stored Procedure - Return individual dates between start date and end date

Hi hopefully someone can help.

I created the following function to return all dates between a start date and end date:

 

ALTER FUNCTION fnGetDatesInRange
(
      @FromDate		datetime,
      @ToDate			datetime
)

RETURNS @DateList TABLE (Dt datetime)
AS
BEGIN

      DECLARE @TotalDays	int
      DECLARE @DaysCount	int

      SET @TotalDays =  DATEDIFF(dd,@FromDate,@ToDate)
      SET @DaysCount = 0
      
		WHILE @TotalDays >= @DaysCount            
		BEGIN	
				INSERT INTO @DateList
				SELECT (@ToDate - @DaysCount) AS DAT
				
				SET @DaysCount = @DaysCount + 1
		END
		RETURN
END  

So calling the following:

SELECT Dt from fnGetDatesInRange('01/25/2008', '02/05/2008')
ORDER BY Dt

Returns:

Dt                      
----------------------- 
25/01/2008              
26/01/2008              
27/01/2008              
28/01/2008              
29/01/2008              
30/01/2008              
31/01/2008              
01/02/2008              
02/02/2008              
03/02/2008              
04/02/2008              
05/02/2008     

Now I have table that contains events. Each event has a StartDate and an EndDate.

I need to select each record from the events table and pass the StartDate and EndDate of each event into the above function.

What I need to end up with is a list of individual dates between the StartDate and EndDate of each event. So I may have:

01/01/2008 - event 1
01/02/2008 - event 1
01/03/2008 - event 1
01/04/2008 - event 1
01/05/2008 - event 1
01/06/2008 - event 1
01/07/2008 - event 1
06/15/2008 - event 2
06/16/2008 - event 2
06/17/2008 - event 2
09/04/2008 - event 3
09/05/2008 - event 3

(I am showing event names just in order to explain - i only want the query to return dates)

Finally I want to filter these results by a start date and end date.

In case you are wondering what I am trying to achieve, I am using the above information to format a calendar based on whether an event occurs on different dates. Since the calendar will only display 1 month at a time, and an event can have a start date and end date that span accross more than one month, I need to look at the individual dates between the start date and end date and check these values to see if they fall between the start and end of the month. From here i can then build up an array of dates with events during a 1 calendar month period and format the calendar control accordingly.

Hope that makes sense,

Thanks,
Ben 
 

  

 


Ben Foster | RetroViz
0
retroviz
2/18/2009 4:44:27 PM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

11 Replies
3053 Views

Similar Articles

[PageSpeed] 2

I don't see a problem for your first question.

Would

select Events.Name, Events.StartDate, Events.EndDate, MyDates.dt from Events CROSS APPLY dbo.fnGetDatesInRange(Events.StartDate, Events.EndDate) myDates

work for you?

SQL Server 2005 and up.


Beware of bugs in the above code; I have only proved it correct, not tried it.
(Donald Knuth)
0
Naom
2/18/2009 4:54:39 PM

 Hi,retroviz

 

retroviz:

What I need to end up with is a list of individual dates between the StartDate and EndDate of each event. So I may have:

01/01/2008 - event 1
01/02/2008 - event 1
01/03/2008 - event 1
01/04/2008 - event 1
01/05/2008 - event 1
01/06/2008 - event 1
01/07/2008 - event 1
06/15/2008 - event 2
06/16/2008 - event 2
06/17/2008 - event 2
09/04/2008 - event 3
09/05/2008 - event 3
 

 

 

U can Do that With cross apply 

Try below example related ur case

 

create table tblEvents
(tblEventsId int identity ,[Event] varchar(50),StartDate datetime ,EndDate datetime)
GO
insert into tblEvents
Select 'event1','01/01/2008','01/07/2008'
union all
Select 'event2','06/15/2008','06/17/2008'
union all
Select 'event3','09/04/2008','09/05/2008'
Go

Select Dt,[Event] from tblEvents 
cross apply fnGetDatesInRange (StartDate,EndDate)
order by Dt,[Event]

GO


Dt                      Event
----------------------- --------------------------------------------------
2008-01-01 00:00:00.000 event1
2008-01-02 00:00:00.000 event1
2008-01-03 00:00:00.000 event1
2008-01-04 00:00:00.000 event1
2008-01-05 00:00:00.000 event1
2008-01-06 00:00:00.000 event1
2008-01-07 00:00:00.000 event1
2008-06-15 00:00:00.000 event2
2008-06-16 00:00:00.000 event2
2008-06-17 00:00:00.000 event2
2008-09-04 00:00:00.000 event3
2008-09-05 00:00:00.000 event3
  

 

 

 


RAGHAV

MVP ASP/ASP.Net Read My Blog


MARK THE POST AS ANSWER IF IT HELPS U.


"Success doesn't come to you…you go to it."--Marva Collins




"Success does not come to those who wait . . . and it does not wait for anyone to come to it." Anonymous


0
raghav_khunger
2/18/2009 4:57:17 PM

Thank you to both of you. I have not even heard of CROSS APPLY before - in fact I am currently working through my MCTS in SQL Server 2005 and it hasn't even been mentioned Indifferent

The final stored proc is as below:

 

ALTER PROCEDURE dbo.dtsTest
(
	@DriverID		int,
	@StartDate	datetime,
	@EndDate		datetime
)
AS
SELECT Dt from tss_DriverDiary
CROSS APPLY fnGetDatesInRange(StartDate, EndDate)
WHERE 
(DriverID = @DriverID) AND (Dt BETWEEN @StartDate AND @EndDate)

Before I close the case, are there any major performance impact to using this approach.

If I am showing the diary for one person then I should only ever have a maximum of 31 records (since I am passing in the start date and end date of just one month).

However, there will be an option to view a diary for all people. If I GROUP BY Dt this should only return one result per day which works for the formatting - i.e. 2 people could be on holiday on one day so only one day value will be returned to show that people have an event on that day.

My understanding of using

SELECT Dt from tss_DriverDiary
CROSS APPLY fnGetDatesInRange(StartDate, EndDate)
WHERE 
(Dt BETWEEN @StartDate AND @EndDate)

Is that the where clause is applied after the entire table has been returned?

Thanks again,

Ben

 


Ben Foster | RetroViz
0
retroviz
2/18/2009 6:15:12 PM

I won a SQL Server magazine on last (and the only so far) SQL Server UG Meeting I attended. I think the topic of UDFs was discussed there, I need to search for this magazine.

Also if memory serves the exact same problem was discussed in tek-tips forum, but I'm not sure I would be able to find it.

Let me start from trying to search for the magazine. Update. Could not find it yet at home, probably left at work.


Beware of bugs in the above code; I have only proved it correct, not tried it.
(Donald Knuth)
0
Naom
2/18/2009 6:23:23 PM

 The problem here (from a performance perspective) is that you would be calling this function for each *something*, and this function has a loop in it, and... each time you call it, the results would be the same.  That's a whole lot of repetition for the same data.

 Instead, I would recommend you have a numbers/calendar table in your database.  If you go with the calendar table approach, there are some things you need to consider.  For example, each row in the table should represent each day, and there should be enough rows in this table to accommodate any data you throw at it.  So.... Jan 1, 1980 to Dec 31, 2200 should do nicely.  That's roughly 220 years, with (on average) 365 days per year. Or... 80,000 rows.  Each Row would take 8 bytes (to store a DateTime), so that would be approximately 640 kilobytes. By today's standards, this is VERY SMALL.

Now, with a calendar table, your query becomes trivial.

To create a calendar table...

 

Create Table Calendar(CalendarDate DateTime Not NULL)
Alter Table Calendar Add Constraint PK_Calendar Primary Key (CalendarDate)

Declare @Start DateTime
Set
@Start = '19900101'

While @Start < '22001231'
Begin
Insert Into
Calendar(CalendarDate) Values(@Start)
Set @Start = @Start + 1
End

  

With the calendar table, this query become simple, like this...

 

  

Select E.*, Calendar.CalendarDate
From Events E
Inner Join Calendar
On Calendar.CalendarDate Between E.StartDate And E.EndDate
 Of course, you wouldn't want to select all the columns, and you probably want to filter on the date, but you get the idea, right?

-George
0
gmmastros
2/18/2009 6:54:01 PM

I found this thread http://tek-tips.com/viewthread.cfm?qid=1528202 which seems to be an interesting approach.


Beware of bugs in the above code; I have only proved it correct, not tried it.
(Donald Knuth)
0
Naom
2/18/2009 6:58:42 PM

Yes, sounds like much better and simplier approach than UDF.


Beware of bugs in the above code; I have only proved it correct, not tried it.
(Donald Knuth)
0
Naom
2/18/2009 7:01:45 PM

I started to look into how often I would need to calculate this information.

Whilst the events table contains details of the event, most of the time I am either showing individual days on which an event occurs or performing a count of specific event types for all people or individuals.

It makes sense then, based on gmmastros suggestion to use an addition table. However, I do not need to prepopulate this with data. Instead I would have my tables set as below:

 

DriverDiary
-----------------------------
DiaryEntryID	int
DriverID	int
Type		tinyint
StartDate	smalldatetime
EndDate		smalldatetime
HalfDay		bit
Description	nvarchar(256)

DriverDiaryDays
-----------------------------
DiaryDayID	int
DiaryEntryID	int
DiaryDate	smalldatetime

Since I am only really concerned with what the people (drivers) are doing in work days (Monday to Friday) I can change my fnGetDatesInRange function to only return "work" days.

When I insert a new record into DriverDiary, within my procedure I can then insert the work days into DriverDiaryDays.

This then makes it easy to do a number of things:

1) My original problem of displaying all days with events within a 1 month calendar period (cater for events that can span over two months). This can now be achieved with a simple select query on the DriverDiaryDays table. If doing for an individual driver I can get the DriverID from the parent DriverDiary table.

2) A count of all event days of a specific type (for an individual or all drivers). Can now be done on the DriverDiaryDays table.

Each person (driver) will only have a maximum of 25-30 holiday events in DriverDiaryEntry. The only other event type is Sick Day so lets say in total each driver would only have 40 entries per year. With approximately 50 drivers we have 2000 records per year. Since we would only have up to 10 people on holiday at any one time it makes the above queries quite efficient.

I want to thank everyone for their suggestions as it helped me arrive at this solution.

Thanks,

Ben


Ben Foster | RetroViz
0
retroviz
2/19/2009 9:01:12 AM

In case it helps anyone this is the function I used to get the Working dates in a range:

 

CREATE FUNCTION fnGetWorkingDatesInRange
(
      @FromDate		datetime,
      @ToDate			datetime
)

RETURNS @DateList TABLE (Dt datetime)
AS
BEGIN

      DECLARE @TotalDays	int
      DECLARE @DaysCount	int
      DECLARE @DayName		nvarchar(10)

      SET @TotalDays =  DATEDIFF(dd,@FromDate,@ToDate)
      SET @DaysCount = 0
      
		WHILE @TotalDays >= @DaysCount            
		BEGIN	
				SET @DayName = DATENAME(dw, (@Todate - @DaysCount))
				
				IF (NOT @DayName = 'Saturday') AND (NOT @DayName = 'Sunday')
				BEGIN
					INSERT INTO @DateList
					SELECT (@ToDate - @DaysCount) AS DAT
				END
				SET @DaysCount = @DaysCount + 1
		
		END
		RETURN
END  
      
 
Ben Foster | RetroViz
0
retroviz
2/19/2009 9:58:02 AM

 There are many advantages to using a calendar table.  For example, you could add a bit column to represent working days.  If the date falls on a weekend, set the bit column to 0 (false).  You could then set the WorkingDay column to false for holidays too. 

 

I think you misunderstood my point about the size of this table.  In my opinion, this is an extremely small table and will perform very well.  In fact, I suspect it will perform better than your function.  When you use a function in a query, you need to be concerned about performance.  For each row in the outer query (where you use the function), the function needs to be evaluated.  If your function is called for a single event, you will probably not notice any performance difference, but when you repetitively call the function, performance will quickly degrade.

I performed a quick test.  First, I modified the table a little.

  

Alter Table Calendar Add WorkDay Bit NULL

Update Calendar 
Set    WorkDay = Case When DateName(dw, CalendarDate) In ('Saturday','Sunday') 
                      Then 0 
                      Else 1 End

 Then, to test performance, I needed to loop a little, so I did this....

  

Declare @Start DateTime

declare @i int
Set @i = 1

declare @Temp Table(Data DateTime)

Set @Start = GetDate()
While @i < 1000
	Begin
		Insert Into @Temp(Data)
		Select dt from dbo.fnGetWorkingDatesInRange('20090101', '20090201')
		Set @i = @i + 1
	End

Select DateDiff(Millisecond, @Start, GetDate())

Declare @Temp2 Table(Data DateTime)
Set @Start = GetDate()
Set @i = 1
While @i < 1000
	Begin
		Insert Into @Temp2(Data)
		Select CalendarDate 
        From   Calendar 
        Where  CalendarDate Between '20090101' And '20090201'
               And Workday = 1
		Set @i = @i + 1
	End

Select DateDiff(Millisecond, @Start, GetDate())

Select Count(*) From @Temp
Select Count(*) From @Temp2

 

Each method returned exactly the same data.  The function took 0.56 seconds (for 1000 iterations) while the Calendar table method took 0.11 seconds (for 1000 iterations).


-George
0
gmmastros
2/19/2009 2:21:00 PM

The article I mentioned yesterday is in SQLServer magazine March/April 2008 issue.

It's called "Optimize Your Query with UDF" by Wayne Fillis.

I'm not sure if it's published online, try to search for it.


Beware of bugs in the above code; I have only proved it correct, not tried it.
(Donald Knuth)
0
Naom
2/19/2009 2:51:01 PM
Reply:

Similar Artilces:

Dates, Dates, Dates!
Hello all... I am in the process of building an appointment system, and I am trying to see if I can get some pointers on which way to go on this. The first version of this appointment system was very lo-tech, and it stored all of the possible dates/times in the database. Once a user booked an appointment, their information was logged to that date/time, and then that date/time would no longer show in the list of possible openings. This was great for a temporary fix, but with having to pre-populate all of the dates/times, the system would eventually have to be updated to continue on pa...

How do I compare a date field to current date in a sql stored procedure?
In Coldfusion, I would compare a date field to the current date with the following code, but how do I do this in SQL for my vb.NET page?  '<cfoutput>#DateFormat(Now(), "MM/DD/YYYY")#</cfoutput>'Never make important decisions on a Monday! In SQL: datediff(day,@Date,getdate())=0 , here @Date is the date what you got from VB.NET Code. In VB.NET Date.ToString() = DateTime.Now.ToShortDateString(); Does these answer what you asked for.Avi Hello, This is a code snippet that shows the sql comparison.we use getdate() function of sql server to get the curre...

calendar date span (date start : date end)
Hi! Can anyone help me with this? Say, I have these two textboxes. Date Start:        Date End:             What I wanted to do is to put dates on it using the calendar. My problem is I do not know what to do to keep the "Date Start" not way after the set "Date End", and for "Date End" not way before the set "Date Start".   example: Date Start: July 1, 2008              &nb...

Start date and End Date
Hi, Working on a create user workflow i need to add a mandatory start date for the user and an optional end date for some users. I have two fields for this in the request form of type date and control Type is DatePicker. There is an eDirectory attribute called loginActivationTime, is that used for activating an account at the preset time? End date, is that Login Expiration Time ? And I think I have to repormat the dates as well ? I have found some discussions about date formats but what is the best practice on how to do a reformat if I use IDM 3.6 and UA3.6.1 ? I guess I...

from date to date
I am working on a form for livestock. one of the things in the form is a checkbox to let me know if they qualify or not. the second is I have a created record field entered into the database... now my question is I am doing a search on this and cant figure it out since i am new to all this...i have a query that selects the qualification checkbox (where qualify = 'true') then another query that selects the created record date... and the search is that I want to get the information in the livestock that are qualifyed (have the checkbox qualify = to 'true') between  one dat...

From Date and To Date
 In my application i have two textboxes.One for From date and the other one for To date.I need to check whether the From date is lesser than To Date..Date formats will be either(DD-MM-YYYY) or (DD/MM/YYYY).and i have used calendar extender for selecting dateI need the script code to do this...Thanks in advance See this thread: http://forums.asp.net/t/1346328.aspxMohsin Naeem"Please mark as answer if it helped you"  <form id="form1" runat="server"> <asp:ScriptManager ID="ScriptManager1" runat="server"> </...

SQL stored procedure with dates
A table in my database has a field of type smalldatetime called "PeriodEnd". I want to write a simple stored procedure that gets records based on the 'PeriodEnd' field. When I run a query such as Select * from mytable where PeriodEnd='30/06/1989' I get an errror saying "The conversion of char data type to a smalldatetime data type resulted in an out-of-range smalldatetime value" Do I have to use CONVERT in some way to alter my input value? cheers Modify your query as follows: select * from shopper where created = '06/30/1989' Om Prakash If you want t...

End date before start date bug
I am trying to enter an event lasting from 20 to 26 October 2004, but Calendar won't let me do it, saying the end date is before the start date. I can enter October 25 or 27 October without problem. Is this a known bug? -- Herbert Eppel www.HETranslation.co.uk Herbert Eppel wrote: > I am trying to enter an event lasting from 20 to 26 October 2004, but > Calendar won't let me do it, saying the end date is before the start date. > > I can enter October 25 or 27 October without problem. > > Is this a known bug? > Herbert et al, works...

help about ---store date in sql database using stored procedure
hi,friends i need your help. i want to store a date into sqlserver database using stored procedure. when i run app. it will give this error....... ---------------------------------- Server Error in '/aspnet/espms' Application. -------------------------------------------------------------------------------- String was not recognized as a valid DateTime. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Det...

Stored Procedure
Having a little trouble getting this one to work. I have a stored procedure that selects items from the table... SELECT fldDate, shortTitle, longTitle, Email,  POC, News, guidFROM tblNews I need to change it so it selects only the year needed.  For instance, only items from 2004. SELECT fldDate, shortTitle, longTitle, Email,  POC, News, guidFROM tblNews WHERE fldDate = @myParameter -Say myParameter = 2004 or whatever year is needed The table field fldDate is a datatime field that has date and time stored and I can put any parameter into the stored procedure. H...

How to handle a start date and end date.. in a database..
hi,         I am getting a data in a dbf file and they have a StartDate and end Date for where the Statments are valid for.. How can i incorporate them into the database.. right now We are doing with PeriodId.. like while the user imports the data they select which period they want to import the data into.. But now if i have to get those details from the Database how do i store it in the Database..and i need to store them in multiple tables.. Thanks Karen I don't understand your request... could you include a row or two of your data? Are you sayin...

Formatting date SQL date to remove time
Hi,I need a way of changing the following SQL statement so that the dates are without the hh:mm:ss tt:"Select DISTINCT([StartDate]) From [Events]"How can this be done?Thanks,Curt. Regards, Curt SELECT CONVERT (VARCHAR, [StartDate], 101) FROM ... For other date formatting types, see http://msdn2.microsoft.com/en-us/library/ms187928.aspxMark replies as answers if they helped you solve the problem. use the following Conversions Convert("urdatecolumn",Varchar,101); and there are more codes for the dateconversions.Its general Syntax is Convert("urdatecolumn",...

javascript validation for start date and end date
Hey Guys! I have two textbox, one for displaying start date and another for end date. These dates are displayed in the respective textboxes when I clicked in the calendar control's date. So, when i select the  date from first calendar, first textbox is filled with the selected date i.e. the start date and similary the second date is displayed in the second textbox after selecting in the second calendar. So, now how can i validate start date and end date?.  Let say I have selected start date from calendar  as (10/8/2007) and it will fill up the first textbox...

How to build a range of dates from a start and end date.
Hi, I have a start and end date in the format of "m/dd/yyyy" I've figured out how to get the amount of days inbetween using the DateDiff() function. My problem is how would I go about building those start and end dates + the dates in between. I need to build them in a format like this "firstDate,secondDate,thirddate" etc. etc. A comma delimited list. Any help would be greatly appreciated. Cheers! Rob C can you give an xample.. ::I need to build them in a format like this "firstDate,secondDate,thirddate" etc. etc. if your firstdate...

Get Date and compare with Date.Now in T-SQL
I am trying to set an alert to user for a project which will due on the particular date. How to compare the DateTime due date  which I get from the database with the Date.Now in stored procedure? Thanks in advance.Best regards,Ron. Make sure they are both in the same format. Many different ways to accomplish. The example below would convert both to a date: select * from table where convert(nvarchar(15),DueDate,101) = convert(nvarchar(15),Date.Now(),101) Thanks samsta, but I got an error when I am using Date.Now(). Then, I change with GetDate() function and works just ...

Web resources about - SQL Stored Procedure - Return individual dates between start date and end date - asp.net.sql-datasource

Gymnastics at the 2012 Summer Olympics – Women's artistic individual all-around - Wikipedia, the free ...
... go through to the Team final, where three team members compete on each apparatus. Teams are ranked on their total score. The best 24 individual ...

The nature of the individual market, games and SEP
The individual insurance market in the United States has always been a funny beast. One part of the market has always been a market for long ...

Is Stephen Curry Having the Best Individual Season in NBA History?
Stephen Curry is having the kind of season that doesn't feel real. It's difficult to explain, let alone understand. The NBA , quite simply, has ...

"White Americans, their activities and ideas seem always to stem from a font of principled and committed ...
"As such, group suspicion and presumed guilt are readily perceived and described as unjust, unreasonable and unethical," writes Janell Ross, ...

AG Lynch Explains Obama’s Gun-Control: Gov’t Looking For ‘Individuals Who Seek To Avoid Registering’
AG Lynch Explains Obama's Gun-Control: Gov't Looking For 'Individuals Who Seek To Avoid Registering'

Tax Credits For Low-Carbon Individuals & Businesses
... electricity sources? How can they partner to get more people in transit and on bikes? How can they [&hellip Tax Credits For Low-Carbon Individuals ...

Trump's doctor: Trump 'will be healthiest individual ever elected' president
CNN Trump's doctor: Trump 'will be healthiest individual ever elected' president CNN Washington (CNN) Donald Trump on Monday released a letter ...

Curry on individual awards: 'Beauty of basketball is it's a team sport'
... Search form Search 42° Navigation Home Giants A's Sharks Warriors Kings 49ers Raiders Quakes Insiders More Tickets Shop Watch Curry on individual ...

Congressmen Move To Protect Silicon Valley, But Ignore Individual Privacy
Congressmen Move To Protect Silicon Valley, But Ignore Individual Privacy

Trump's personal doctor: Trump would be 'the healthiest individual ever elected to the presidency'
Good news: Donald Trump's doctor says Donald Trump would be the "healthiest individual ever elected to the presidency." He's just that super-healthy ...

Resources last updated: 1/7/2016 9:00:48 PM