Previous Month Last Date

Ashish · Post by **Ashish** » Wed Aug 19, 2009 10:48 pm

Hi,

How to get the last date of the previous month.?

Thanks
Ashish

talk2shaanc · Post by **talk2shaanc** » Wed Aug 19, 2009 11:27 pm

DateFromDaysSince(-1, stringtodate(right(0:MonthFromDate(CurrentDate()),2):'-':'01':'-': YearFromDate(CurrentDate()),"%mm-%dd-%yyyy"))

dxk9 · Post by **dxk9** » Wed Aug 19, 2009 11:33 pm

Does this give the date of last day or the 1st day of previous month? When I manipulate it, I get the 1st day of previous month. If its last day, can you explain me the logic?

Regards,
Divya

laknar · Post by **laknar** » Thu Aug 20, 2009 2:31 am

SELECT TRUNC(SYSDATE)-1 AS LAST_MONTH_END_DATE FROM DUAL in oracle

dxk9 · Post by **dxk9** » Thu Aug 20, 2009 3:06 am

Laknar,
Your query give yesterday's date only. Moreover I think the requirement is in datastage and not oracle.

Regards,
Divya

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Thu Aug 20, 2009 3:12 am

Make it

Code: Select all

trunc(sysdate, 'mm') - 1

then.

dxk9 · Post by **dxk9** » Thu Aug 20, 2009 3:16 am

In oracle, that query is fine. But I think Ashish requires in datastage. Correct me if I am wrong.

Regards,
divya

priyadarshikunal · Post by **priyadarshikunal** » Thu Aug 20, 2009 3:30 am

talk2shaanc wrote:DateFromDaysSince(-1, stringtodate(right(0:MonthFromDate(CurrentDate()),2):'-':'01':'-': YearFromDate(CurrentDate()),"%mm-%dd-%yyyy"))

in datastage this should work fine.

this first derives the 1st day of current month and then subtracts 1 day from that.

in oracle sainaths code works fine.

dxk9 · Post by **dxk9** » Thu Aug 20, 2009 3:35 am

Thanks for the explanation priyadarshikunal.

Regards,
Divya

Ashish · Post by **Ashish** » Thu Aug 20, 2009 4:23 am

priyadarshikunal wrote:
talk2shaanc wrote:DateFromDaysSince(-1, stringtodate(right(0:MonthFromDate(CurrentDate()),2):'-':'01':'-': YearFromDate(CurrentDate()),"%mm-%dd-%yyyy"))
in datastage this should work fine.

this first derives the 1st day of current month and then subtracts 1 day from that.

in oracle sainaths code works fine.

Hi guys,

Thanks for the information

Thanks Ashish