Status: Solved
Priority: Medium
Security: Public
Views: 437
Last Modified: 2014-01-17

Need to get the last day of the month (Dec 31) for 2012 not 2013?

Hello, I'm trying to get the last day of the month for the prior year 2012 instead of 2013.

Thanks

Example

select * from hsp_bkt_aging_hx

where hsp_bkt_aging_hx.aging_date = LAST_DAY(ADD_MONTHS(TRUNC(TRUNC(SYSDATE , 'Year')-1 , 'Year'),11))

MIREESE

Asked: 2014-01-17

Advertise Here

Oracle Database

1 Solution

LVL 74

sdstuberCommented: 2014-01-17

trunc(sysdate,'yyyy') -1

will return Dec 31 2013

add_months(trunc(sysdate,'yyyy') -1,-12)

will return Dec 31 2012

you could also use

trunc(trunc(sysdate,'yyyy')-1,'yyyy')-1
or
trunc(sysdate,'yyyy') - 1 - interval '1' year

LVL 32

awking00Commented: 2014-01-17

To get the last day of the year before last -
select add_months(trunc(sysdate,'yy') - 1,-12) from dual;
Can you provide a little more description of what you need given different current dates?

MIREESEAuthor Commented: 2014-01-17

Thanks This worked!!

Since this is only an issue in January, How could I code this to look back 2 years only during January and only look back 1 year the after January?

Example

If Month = Jan aging date = last month of 2012 else

LAST_DAY(ADD_MONTHS(TRUNC(TRUNC(SYSDATE , 'Year')-1 , 'Year'),11)) /*2013*/

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Need to get the last day of the month (Dec 31) for 2012 not 2013?

Join & Write a Comment Already a member? Login.

Featured Post