sql oracle case when date

Question

This is what I got so far:

select
to_char(sysdate, 'yyyy') Time
from 
dual;

Which gives me:

TIME

2015

Its working until this point.

I would like to add

• if the month is >= 7 I get as output 01.07.current year
• if the month is <= 7 I get as output 01.07.(current year - 1 year)

Any ideas how to handle this? I thought about CASE WHEN but I dont get know how.

Thanks!

Solution 1

A simple CASE expression would do the job.

For example,

SQL> SELECT
  2    '01.07.' ||
  3    CASE
  4      WHEN TO_CHAR(SYSDATE, 'MM') < '07'
  5      THEN 
  6        TO_CHAR(SYSDATE, 'YYYY')
  7      ELSE
  8        TO_CHAR(add_months(SYSDATE,-12), 'YYYY')
  9    END case_date
  10 FROM dual;

CASE_DATE
----------
01.07.2015

SQL>

To keep it even more precise, you could keep the common value outside the CASE expression:

SQL> SELECT '01.07.'
  2    ||
  3    CASE
  4      WHEN TO_CHAR(SYSDATE, 'MM') < '07'
  5      THEN TO_CHAR(SYSDATE, 'YYYY')
  6      ELSE TO_CHAR(add_months(SYSDATE,-12), 'YYYY')
  7    END case_date
  8  FROM dual;

CASE_DATE
----------
01.07.2015

SQL>

Solution 2

Using extract more readable

SELECT
 to_date((CASE
             WHEN extract(MONTH FROM SYSDATE) >= 7 THEN
              0
             ELSE
              -1
         END) + extract(YEAR FROM SYSDATE) || '07-01', 'yyyy-mm-dd') END
  FROM dual

Comments

[piguy]

Thanks! That helped me a lot! Perfect

[Lalit Kumar B]

@Okdel Thanks for suggesting an edit. Since there was content being lost from the explanation part, I have to improve it myself. So, I accepted your edit and improved it.