Another 'Get last month's data' question

[elsenorjose]

I'm using the following syntax to retrieve data for the period between Sunday and Saturday of the prior week regardless of what day I run this query in the current week.

...WHERE YEARweek(tblDate) = YEARweek(CURRENT_DATE - INTERVAL 7 DAY)

What is the corresponding syntax to retrieve records between the first and last day of the prior month, regardless of when in the current month I run this?

Thanks in advance.

 

[r937]

where tblDate 
       >= date_sub(
          date_sub(current_date
              , interval dayofmonth(current_date)-1 day)
              , interval 1 month)
  and tblDate 
        < date_sub(current_date
              , interval dayofmonth(current_date)-1 day)

r937.com | rudy.ca

 

[elsenorjose]

Wow...
Whole lot easier to do it for a week, eh?
Thanks!

 

[r937]

well, i suppose

however, if you apply a function to a column, then mysql cannot use an index on that column, and so has to do a table scan

won't matter until you get large volume, at which point the query will get slower and slower...

r937.com | rudy.ca

 

[elsenorjose]

This function will be used to create a view. The table is already very large so I'm summarizing by week and month ranges.