How do I set the date in a sql query so that it always set for a range of the entire month prior?
Posted on 2007-10-18
I'm looking for a way to set the date range of the following query so that it is always the range from the first calendar day to the last calendar day of the previous month. I know if I use sysdate-1 to sysdate-30 and run the query on the first day of the current month, in some cases, the 30 day months that are prior, will have a complete date range. But what about the 31 day months and February? Is there a date function like SYSMONTH that can be used to define the range I want? Here is my current query:
from ap_checks_all ac
where ac.org_id = hou.organization_id
and ac.bank_account_id = aba.bank_account_id
and aba.bank_branch_id = abb.bank_branch_id
and ac.vendor_id = pv.vendor_id
and pv.vendor_id = pvs.vendor_id
and ac.vendor_site_id = pvs.vendor_site_id
and aba.bank_account_num = '950002808'
and ac.check_date between '01-SEP-07' and '30-SEP-07'
order by ac.check_date desc