List of Days between 2 dates

[reginab]

I need code to get an array of dates between 2 dates. For instance 2/1/2009 through 2/10/2009. I want 2/1/2009, 2/2/2009, 2/3/2009 etc. They are stored in my database as the Beginningdate and EndingDate. Can anyone help with this.


Does anyone have any suggestions on how to do this?

 

[John Big Booty]

Build your self a query and put the following in Criteria for your date field;

>=2/1/2009 And <=2/10/2009

 

[pbaldy]

Put the two dates into variables, and run a Do While loop on them. Inside the loop, add the current variable value to your array (or whatever) and increment it a day.

 

[reginab]

Thanks PBaldy,but I really don't know how to start. I must admit I'm not a great coder. I was able to get what I needed by using IIF statements in a query but now i'm stuck because I also have to eliminate the weekends.

 

[pbaldy]

I'm not sure how IIf() statements would have gotten you that, so maybe you should clarify the goal. Maybe I was on the wrong track.

 

[reginab]

PBaldy, I solved my issue by creating 2 SQL views in SQL Server 2005. The first one creates a crosstab of all dates between the beginning date and the ending date. The second is a union query that puts all the data into one column and selects out the dates that are Saturday and Sunday.

this is a snippet of the first view

SELECT RecID, EmpID, BeginDate, EndDate, 'Date1' =
CASE
WHEN DATEDIFF(day,BeginDate,EndDate+1) >= 1 Then [BeginDate]
End,
'Date2' =
CASE
WHEN DATEDIFF(day,BeginDate,EndDate+1) >= 2 Then [BeginDate] +1
End
From tblFTODetail

Here's a snippet of the second view

SELECT RecID, EmpID, BeginDate, EndDate, Date1 AS RequestDate
FROM vw_CrosstabRequestDates
WHERE (Date1 IS NOT NULL) And DatePart(dw,Date1) <> 7 and DatePart(dw,Date1) <> 1

Union SELECT RecID, EmpID, BeginDate, EndDate, Date2 AS RequestDate
FROM vw_CrosstabRequestDates
WHERE (Date2 IS NOT NULL) And DatePart(dw,Date2) <> 7 and DatePart(dw,Date2) <> 1
from FTODetail

Here's the result for one employee: The last field is what I need

1 888888 10/12/2009 12:00:00 AM 10/16/2009 12:00:00 AM 10/12/2009
1 888888 10/12/2009 12:00:00 AM 10/16/2009 12:00:00 AM 10/13/2009
1 888888 10/12/2009 12:00:00 AM 10/16/2009 12:00:00 AM 10/14/2009
1 888888 10/12/2009 12:00:00 AM 10/16/2009 12:00:00 AM 10/15/2009
1 888888 10/12/2009 12:00:00 AM 10/16/2009 12:00:00 AM 10/16/2009

Works like a charm and no code required.

Thanks for attempting to help me

 

[ma t]

I know this message is very old, but can PBaldy give a bit more clarification for us novices?

Put the two dates into variables, and run a Do While loop on them. Inside the loop, add the current variable value to your array (or whatever) and increment it a day.

I have a student class enrollment table with start and end dates for the time period the student is enrolled in a certain class... i have a form by which the user can find all students who were enrolled in a class in a certain date range... I would like to generate a list of dates between enrollment start and enrollment end, and then see if any of those dates are between the report start or end dates. All help is greatly appreciated.

 

[pbaldy]

The basics:

  Dim dteStartDate            As Date
  Dim dteEndDate              As Date
  Dim dteCurrDate             As Date

  dteStartDate = #7/1/2013# 
  dteEndDate = #7/10/2013# 

  For dteCurrDate = dteStartDate To dteEndDate
    Debug.Print dteCurrDate
  Next dteCurrDate

 

[ma t]

Thank you PBaldy, i might need more than basics
do dteStartDate and dtrEndDate refer to my search fields in the form, or the fields in my enrollment info table? I am sorry for my lack of understanding. Thank you for trying to help me!