Getting a date range for the Previous Month programatically 2

[LeonelSanchezJr]

I need to create a WHERE clause which should return a date range for the previous month.

This procedure will always be run on the 7th of each month.
So, if it is August 8, 2004, the WHERE date range should be 07/01/2004 through 07/31/2004

How can I do this through a PL/SQL statement?

Thanks,

Leo ;-)

 

[jaggiebunnet]

Here is the sql doing most of what you need, you should be able to customise as required:

jaggie@DEV>SELECT SYSDATE, ADD_MONTHS(SYSDATE, -1), TRUNC(SYSDATE, 'mm'), ADD_MONTHS
(TRUNC(SYSDATE, 'mm'), -1)
  2  FROM dual
  3  ;

SYSDATE   ADD_MONTH TRUNC(SYS ADD_MONTH
--------- --------- --------- ---------
06-AUG-04 06-JUL-04 01-AUG-04 01-JUL-04

 

[LeonelSanchezJr]

That didn't work.


Thanks,

Leo ;-)

 

[jaggiebunnet]

What didn't work exactly?

 

[LeonelSanchezJr]

It was my mistake, I included your line numbers 2 and 3.
It did give me 7/1/2004, but I need it to return the last day for July as well. example 7/31/2004.


Thanks,

Leo ;-)

 

[jaggiebunnet]

try TRUNC(SYSDATE, 'mm') - 1

 

[LeonelSanchezJr]

Okay, how would I get the last day of the previous month?

Thanks,

Leo ;-)

 

[jaggiebunnet]

This should give you the start of last month and the end of last month, using sysdate as the current month.

SELECT u.object_name, ADD_MONTHS(TRUNC(SYSDATE, 'mm'), - 1) start_month,  TRUNC(SYSDATE, 'mm') - 1 end_month 
FROM USER_OBJECTS u
WHERE  created BETWEEN ADD_MONTHS(TRUNC(SYSDATE, 'mm'), - 1) AND TRUNC(SYSDATE, 'mm') - 1

 

[LeonelSanchezJr]

Great; thank you so much.
Have a great day.


Thanks,

Leo ;-)

 

[SantaMufasa]

Leonel,

To obtain the "last day" of any month, displaced from a given date, the simplest way is

1) TRUNC a given date to the first day of its month,
2) ADD_MONTHS (using a negative value) to displace the date to the previous month that is one day beyond your target "end-of-month" date,
3) Subtract one day to obtain the "Last Day" of the prior month.

Here is a coded example that yields the last day of the month just prior to three full months before today:

select add_months(trunc(sysdate,'MM'),-3)-1 from dual;

ADD_MONTH
---------
30-APR-04

Let us know if this gives you the algorithm you need.

Mufasa
(aka Dave of Sandy, Utah, USA @ 16:48 (06Aug04) UTC (aka "GMT" and "Zulu"), 09:48 (06Aug04) Mountain Time)

 

[jaggiebunnet]

em...I thought I had just said that?

 

[SantaMufasa]

Jaggie, my buddy,

I know you've only been a member of Tek-Tips since June 10, so you may not yet be aware of some of the idiosyncracies of this site. One of the idiosyncracies is that there is nothing preventing "dual posts"...Multiple people posting at the same time, saying virtually the same thing. When that happens, it makes the subsequent posters look a bit silly. I'm sure that you'll have chances to look just as silly too. But since we all understand about "dual posts", we take that into consideration and we resist the temptation to say things like, "em...I thought I had just said that?"

Overlapping posts are one reason why I time stamp each of my posts...so people can more easily see why two posts may say the same thing. If everyone did likewise, there would be no question why posts contained similar content. Our Tek-Tips advisory group has discussed the option of automatically time stamping each post (which would easily explain to the casual reader why two posts appear to say the same thing), but until Tek-Tips implements that feature, I invite anyone and everyone to time stamp their posts, as well.

All I can suggest is: relax and cut people some slack, Jaggie, and you (and the rest of us) will enjoy this site much more.

Mufasa
(aka Dave of Sandy, Utah, USA @ 00:36 (07Aug04) UTC (aka "GMT" and "Zulu"), 17:36 (06Aug04) Mountain Time)

 

[lewisp]

Can I suggest at this late stage:

SELECT Last_Day(Add_Months(SYSDATE,-1))
FROM dual;

to get the last day of the previous month?

 

[SantaMufasa]

Lewis,

We done and equally clever ! Have a star.

Mufasa
(aka Dave of Sandy, Utah, USA @ 16:57 (07Aug04) UTC (aka "GMT" and "Zulu"), 09:57 (07Aug04) Mountain Time)