Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 804
  • Last Modified:

How do I set the date in a sql query so that it always set for a range of the entire month prior?

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.bank_account_name
    ,ac.check_number check#
    ,ac.check_date
    ,pv.vendor_name
    ,ac.amount
    ,hou.name facility
    ,to_char(ac.creation_date,'DD-MON-YYYY') entry_date
    ,ac.cleared_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
0
janthonyn
Asked:
janthonyn
  • 3
  • 2
  • 2
  • +1
3 Solutions
 
Angelp1ayCommented:
For the end date can you do something like:

    DATE_ADD(d,-1,MAKE_DATE(01/Mon+1/YY))
0
 
cjohnsen2Commented:
check this out, it has functions which will help you.

http://weblogs.sqlteam.com/jeffs/archive/2007/01/02/56079.aspx
0
 
janthonynAuthor Commented:
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?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
cjohnsen2Commented:
0
 
Angelp1ayCommented:
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.
0
 
Angelp1ayCommented:
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?
0
 
ZuZuPetalsCommented:
SELECT whatever, whatever, DateField
FROM sometable
WHERE Month([DateField]))=Month(Date())-1);

Good Luck!
0
 
janthonynAuthor Commented:
The actual function combo that works in Oracle is this:

ac.check_date between round(add_months(sysdate,-2),'MONTH')and
                            TO_CHAR(last_day(add_months(sysdate,-1)),'DD-MON-YY')

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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now