CODEWITHSUNDEEP
oracle-databaseplsql
Nickknack
Nickknack

Reputation: 857

Change the year of a date to the current year in PL/SQL

I'm currently trying to do a comparison in my select. If the current date is before August 1st of the current year then display august 1st of the last year, otherwise display august 1st of this year. Essentially I'm trying to do:

CASE
   WHEN (SYSDATE < 08/01/2015) THEN
     08/01/2014
ELSE
     08/01/2015

But I am at a loss as to how to get august for the month. So far I have:

TRUNC(SYSDATE, 'MON')

To get /01/ but how would I get it to constantly return august as the month? Would it be better to hardcode in the date and month and dynamically get the year instead? like 01/08/

Upvotes: 1

Views: 2945

Answers (1)

Ditto
Ditto

Reputation: 3344

Try something like this:

    1  select sysdate,
    2         trunc(sysdate,'YEAR'),
    3         add_months(trunc(sysdate,'YEAR'),7),
    4         add_months(trunc(sysdate,'YEAR'),7-12)
    5*  from dual
  SQL> /

  SYSDATE     TRUNC(SYSDA ADD_MONTHS( ADD_MONTHS(
  ----------- ----------- ----------- -----------
  31-jul-2015 01-jan-2015 01-aug-2015 01-aug-2014

  SQL>

the columns are:

1) pulling the current sysdate.

2) converting to the first day of the year.

3) adding 7 months to get Aug 1 of current year.

4) -12 months to get Aug 1 of last year.

(that shows you the usage, you can figure out how to plug those suckers into your CASE statement ;) )

Upvotes: 2

Related Questions