Last Day of month

# Thread: Last Day of month

1. Senior Member
Join Date
Dec 1969
Posts
451

## Last Day of month

Is there a way i can determine the last day of any given month. For example, I need to create a script to generate a SQL statement based on the prior month so <BR><BR>lastmonth = month(date) - 1<BR><BR>SQL = "SELECT * FROM order where date &#062;= &#039;"& lastmonth &" 1, 2005&#039; AND date &#060;=&#039;"& lastmonth &" "?", 2005&#039;"<BR><BR>Any help would be greatly appreciated...

2. Senior Member
Join Date
Dec 1969
Posts
11,247

## RE: Last Day of month

Well two things here Month returns a number<BR><BR>So maybe you want <BR><BR>lastmonth = monthname(month(date) - 1)<BR><BR>But even better<BR><BR>SQL = "SELECT * FROM order where Month([date]) = Month(Datediff(m, -1, Date())) AND Year([date]) = year(Datediff(m, -1, Date()))<BR>

3. Senior Member
Join Date
Dec 1969
Posts
96,118

## Yes, easy...

&#060;%<BR>firstDayOfLastMonth = DateSerial( Year(Date()), Month(Date())-1, 1 )<BR>firstDayOfThisMonth = DateSerial( Year(Date()), Month(Date()), 1 )<BR><BR>SQL = "SELECT * FROM [order] " _<BR> & " WHERE [date] &#062;= &#039;" & firstDayOfLastMonth & "&#039; " _<BR> & " AND [date] &#060; &#039;" & firstDayOfThisMonth & "&#039;"<BR>%&#062;<BR><BR><BR>Notice the change from &#060;= to &#060;<BR><BR>

4. Senior Member
Join Date
Dec 1969
Posts
11,247

## Nearly 10hrs!

that has to be one of your longest -- ;-)

5. Senior Member
Join Date
Dec 1969
Posts
96,118