Last Month Date Criteria 1

[fmrock]

Hey everyone,

I use the following code to pull up accounts that have a date of last week.

AND tablename.DATEUPDATED between next_day(trunc(sysdate)-13,'SUNDAY') and next_day(trunc(sysdate)-6,'SATURDAY')

I want to do the same thing, but for the last full month. So not matter what day the query is run in Feb 2009, I need to pull accounts with a date of jan 2009

 

[SantaMufasa]

FMRock,

There are many good methods to achieve your objective. I like this one:

AND tablename.DATEUPDATED between add_months(trunc(sysdate,'MM'),-1) and trunc(sysdate,'MM')-(1/24/60/60)...

The code means,

...between 1 month before midnight on the morning of the beginning of the current month

AND

1 second prior to midnight on the morning of the beginning of the current month.

Let us know how you like this method.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[fmrock]

Worked perfet. Thanks Mufasa!