How to filter date fields by month?

[leifoet]

This sql outputs an activity table (from an Access database - ASP classic)

sql = "SELECT * FROM Activity ORDER BY Actdate DESC"

Do while Not rs.EOF
...
rs.moveNext
Loop

I am now trying to filter the output 'per month'

- for example, to display only the records of the month of June (=> not working)
Do While Not rs.EOF and month (rs ("ActDate")) = month (cdate ("01/06/2020"))
Do While Not rs.EOF and month (rs ("ActDate")) = 6 (=> not working either)

(This test does work
response.write month (cdate ("01/06/2020")) & "<BR>"
if month (cdate ("01/06/2020")) = 6 then
response.write ("Correct!") & "<BR>"
end if)

- for example, only records 'older' than the month of May => does work !!!
Do While Not rs.EOF and month (rs ("ActDate"))> month (cdate ("01/05/2020"))

but only records 'younger' than the month of July (is not working)
Do While Not rs.EOF and month (rs ("ActDate")) <month (cdate ("01/07/2020"))

(My 'alternative' combination of 'older' and 'younger' doesn't work either)

Thanks for tips to filter 'per month'.

 

[dgillz]

Why not just put a WHERE clause at the end of your SELECT statement?

Macola Software Veteran and SAP Business One Consultant on Training Wheels

Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[Andrzejek]

Did you try:

sql = "SELECT *[blue], MONTH(Actdate) As MyMonth[/blue] FROM Activity ORDER BY Actdate DESC"

Do while Not rs.EOF[blue]
  If rs("MyMonth") = MONTH((CDate("01/06/2020")) Then
    ...
  End If[/blue]
  rs.moveNext
Loop

---- Andy

There is a great need for a sarcasm font.

 

[gmmastros]

I think your problem is with the AND statement.

Do While Not rs.EOF and month (rs ("ActDate")) = month (cdate ("01/06/2020"))

Your DO WHILE loop is ending prematurely. A WHILE loop stops executing as soon as the expression evaluates to false. So, if your first ActDate value is from January (for example), the evaluation becomes this...

Do While Not RS.EOF And Month(RS("ActDate)) = Month(CDate("01/06/2020"))

If the first ActDate represents a date in January, RS.EOF evaluates to False so...

Do While Not False And Month(RS("ActDate)) = Month(CDate("01/06/2020"))

Or

Do While True And Month(RS("ActDate)) = Month(CDate("01/06/2020"))

A date in january, right....

Do While True And Month({Date in January}) = Month({Date in June})
Do While True And 1 = 6
Do While True And False
Do While False

Because the first iteration evaluates to false, your code won't even look at any other rows returned.

The easiest fix is to filter in the query, because databases are very good at that sort of thing.

The next easiest is to separate the loop from the month filter... exactly the way Andy shows in the previous answer.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[leifoet]

I got it and turned the wrong code into a working one ...
Thanks to dgillz, Andrzejek and gmmastros.