0

I had a problem with date manipulation in SQL Oracle. Here's some code. Let's say I have 3 column

Create table anom1(
NAMEID, VARCHAR(20),
DATE_ENTRY, VARCHAR(20),
BIRTH DATE
)

and here's values of nameid, date_entry, and birth

INSERT INTO ANOM1(NAMEID,DATE_ENTRY,BIRTH) VALUES('3404077803080028','12-03-2008',TO_DATE('26-07-1987','DD-MM-YYYY'));
INSERT INTO ANOM1(NAMEID,DATE_ENTRY,BIRTH) VALUES('3404012303080028','01-01-2098',TO_DATE('26-07-2014','DD-MM-YYYY'));
INSERT INTO ANOM1(NAMEID,DATE_ENTRY,BIRTH) VALUES('3404013303080028','01-01-2008',TO_DATE('26-07-2013','DD-MM-YYYY'));
INSERT INTO ANOM1(NAMEID,DATE_ENTRY,BIRTH) VALUES('3404012303080028','01-01-2098',TO_DATE('26-07-1999','DD-MM-YYYY'));

I have 3 cases,

  1. I want to manipulate date where date_entry < birth, change date_entry replace year from birth +1
  2. after that I want to manipulate where date_entry > sysdate, change date_entry year to 2010
  3. condition if date_entry > sysdate and birth < 5 (1-4 year) same condition like numb 1

Example

Case 1

date_entry < birth
01-01-2008  26-03-2015

Case 2

Date_entry > sysdate
01-01-2098  26-03-2015

Case 3

Date_entry > sysdate and birth < year 
26-07-2014  01-01-2098  (01-01-2010) date_entry_dummy

My work :

SELECT NAMEID,BIRTH,DATE_ENTRY,
CASE WHEN DATE_ENTRY < BIRTH THEN
REPLACE(DATE_ENTRY,SUBSTR(DATE_ENTRY,7,4),(EXTRACT(YEAR FROM BIRTH)+1))
WHEN DATE_ENTRY > SYSDATE THEN
REPLACE(DATE_ENTRY,SUBSTR(DATE_ENTRY,7,4),2010)
WHEN DATE_ENTRY > SYSDATE AND TRUNC(FLOOR(MONTHS_BETWEEN(SYSDATE,BIRTH)/12))< 5 THEN
REPLACE(DATE_ENTRY,SUBSTR(DATE_ENTRY,7,4),(EXTRACT(YEAR FROM BIRTH)+1))
ELSE DATE_ENTRY
END DATE_ENTRY_DUMMY FROM ANOM1;

Here's failed result :

NAMEID              | BIRTH     | DATE ENTRY | DATE_ENTRY DUMMY
3404077803080028     26-07-1987   12-03-2008       12-03-2008
3404012303080028     26-07-2014   01-01-2098       01-01-2010
3404013303080028     26-07-2013   01-01-2008       01-01-2014
3404012303080028     26-07-1999   01-01-2098       01-01-2010

How to make year like this :

NAMEID              | BIRTH     | DATE ENTRY | DATE_ENTRY DUMMY
3404077803080028     26-07-1987   12-03-2008       12-03-2008
3404012303080028     26-07-2014   01-01-2098       01-01-2015
3404013303080028     26-07-2013   01-01-2008       01-01-2014
3404012303080028     26-07-1999   01-01-2098       01-01-2010

Need advice in case when condition SQL.

Community
  • 1
  • 1
kyouzano
  • 55
  • 1
  • 7

1 Answers1

1

In a CASE statement each when clause is evaluated in order until a match is found. so in your case the WHEN DATE_ENTRY > SYSDATE will always be matched before the WHEN DATE_ENTRY > SYSDATE AND TRUNC(FLOOR(MONTHS_BETWEEN(SYSDATE,BIRTH)/12))< 5 is evaluated.

If you order your when clauses from more specific to less specific you will get the result you are looking for.

As a side note, be careful with implicit conversions. you should cast your date_entry field to date or a client with a different NLS setting could get different results.

So use TO_DATE(DATE_ENTRY,'dd-mm-yyyy') > SYSDATE

Neil Barsema
  • 201
  • 1
  • 4