How to get the dates between two dates?

[mattboyslim]

I have a database where there is a "Start Date" and and "End Date" of an event.
I need to get all the days between those two days.

The reason I need this is because of a calendaring application where I'm highlighting days where events occur.

Here is my current code, which loops and highlights both the start date and the end date, but I also need the days between those days highlighted.

Code:
=================
oCalendar.AddHightlight(rs_events.Fields.Item("f_datestart").Value)
oCalendar.AddHightlight(rs_events.Fields.Item("f_dateend").Value)
=================

I need something like this, but I'm not sure how to accomplish the coding. I'm assuming that if done correctly, you could do away with the individual start and end dates, and simply get all dates with one string, but again, I may be wrong.
Code:

oCalendar.AddHightlight(rs_events.Fields.Item("f_datestart").Value)
oCalendar.AddHightlight((rs_events.Fields.Item("f_datestart").Value) - (rs_events.Fields.Item("f_dateend").Value))
oCalendar.AddHightlight(rs_events.Fields.Item("f_dateend").Value)

Thanks in advance,
Matt

 

[gmmastros]

Consider adding a calendar table to your database. The following article discusses calendar tables in SQL Server, but the same concept would apply to any relational database.

http://sqlserver2000.databases.aspf...nsider-using-an-auxiliary-calendar-table.html

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[mattboyslim]

That was so ridiculously over my head that I can't even see it. I'm not even sure what it was talking about. I think that might be WAY too much for me to do.

 

[gmmastros]

Ok, then let me exmplain it in broad terms.

Basically, the article is suggesting that you create a permanent Calendar table in your database. This calendar table should have a single record for EVERY date that you could possibly care about.

After you have this table, you can easily get your query to return all dates within a date range (so you can highlight the all the dates between them).

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[mattboyslim]

Ooooh...gotcha'. So that code was to automatically create and populate the SQL database. Any idea where I could find an Access database with those dates already populated? Otherwise I guess I could use Excel to fill down a number of years and import that into Access.

 

[gmmastros]

Using Excel would be a simple way to create the calendar table. Just make sure it has every day in the table. After creating the table in access and populating it with data, you should make the date column be your primary key (because it will create a clustered index to improve the performance).

The other parts of the article explain how you can use this table to indicate holidays, weekends, etc... Depending on your application, this could be a good thing.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[mattboyslim]

OK, so I have a table with only a single field (f_dates), and that field is set as the primary key. I'm not concerned about holidays or weekends, as it is just a calender to highlight "regional events".

Now my primary question, is since all of my events are in a separate database, once I configure my connection, how do I determine what dates are between:
(rs_events.Fields.Item("f_datestart").Value) and
(rs_events.Fields.Item("f_dateend").Value)

So for example:
f_datestart = 1/7/2007
f_dateend = 1/14/2007

Thanks for help so far,
Matt

 

[mattboyslim]

Here is my current code, which is looping through the start and end dates. I suppose I could get confused if I have to loop through these within the current loop statement, so if I have to do that, I may need some assistance with that also:

While ((Repeat3__numRows <> 0) AND (NOT rs_events.EOF)) 
oCalendar.AddHightlight(rs_events.Fields.Item("f_datestart").Value)
oCalendar.AddHightlight(rs_events.Fields.Item("f_dateend").Value)
Repeat3__index=Repeat3__index+1
Repeat3__numRows=Repeat3__numRows-1
rs_events.MoveNext()
Wend

 

[mattboyslim]

Here is what works, without using the calendar dates database:

	While ((Repeat3__numRows <> 0) AND (NOT rs_events.EOF)) 

		dim dStartDate, dCurrentDate, dEndDate
		dStartDate = cDate(rs_events.Fields.Item("f_datestart").Value)
		dCurrentDate = dStartDate
		dEndDate = cDate(rs_events.Fields.Item("f_dateend").Value)
		
		oCalendar.AddHightlight(dStartDate)
		do while dCurrentDate < dEndDate
			dCurrentDate = dateAdd("D",1,dCurrentDate)
			oCalendar.AddHightlight(dCurrentDate)
		loop
		oCalendar.AddHightlight(dEndDate)

	Repeat3__index=Repeat3__index+1
  	Repeat3__numRows=Repeat3__numRows-1
  	rs_events.MoveNext()
	Wend