Go Premium for a chance to win a PS4. Enter to Win

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

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))
0
MIREESE
Asked:
MIREESE
1 Solution
 
sdstuberCommented:
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
0
 
awking00Commented:
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?
0
 
MIREESEAuthor Commented:
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*/
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now