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
Last Modified: 2008-01-09
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:

select abb.bank_name
    ,aba.bank_account_num bank_account#
    ,ac.check_number check#
    , facility
    ,to_char(ac.creation_date,'DD-MON-YYYY') entry_date
from ap_checks_all ac
   ,ap_bank_accounts_all aba
   ,ap_bank_branches abb
   ,hr_operating_units hou
   ,po_vendors pv
   ,po_vendor_sites_all pvs
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
Question by:janthonyn
    LVL 11

    Accepted Solution

    For the end date can you do something like:

    LVL 4

    Expert Comment

    check this out, it has functions which will help you.

    Author Comment

    Thanks for the tips, but this is an Oracle database and doesn't allow the functions sited. Or maybe I'm not using them correctly. Anyone know an Oracle function that I could use for this?
    LVL 4

    Assisted Solution

    LVL 11

    Expert Comment

    Sorry, I was posting air code. The principle is the same though. Am suggesting create a date for 1st of the following month, then subtract 1 day. This should ensure you always have 31st for months with 31 days, 30th for months with 30 days, should even manage Feb 29th on leap years. Hopefully Oracle has some functions that allow you to implement this.
    LVL 11

    Expert Comment

    Do you mean so you get data for the month prior to the current month? e.g. today it would give you 1-Sep to 30-Sep on Nov 3rd say it would give you 1-Oct to 31 Oct?
    LVL 2

    Assisted Solution

    SELECT whatever, whatever, DateField
    FROM sometable
    WHERE Month([DateField]))=Month(Date())-1);

    Good Luck!

    Author Comment

    The actual function combo that works in Oracle is this:

    ac.check_date between round(add_months(sysdate,-2),'MONTH')and

    I appreciated the efforts of all. So... points.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
    'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now