CODEWITHSUNDEEP
sqloracle11g
P.Nichols
P.Nichols

Reputation: 303

Replace date error in oracle sql case statement

I have a table that contains a date error.

The error is a typo that looks like this : 01-AUG-208 instead of 01-AUG-2008. What I am trying to do is create a view from that table with the correct info.

I have tried this but it gives me an ORA-01858: a non-numeric character found where a digit was expected

CREATE OR REPLACE VIEW IC_STRUCTURE("DATE_DE_CONSTRUCTION") AS
SELECT 
  CASE
    WHEN DATE_DE_CONSTRUCTION = '01-AUG-208'
    THEN TO_DATE('01-AUG-2008')
    ELSE DATE_DE_CONSTRUCTION
  END AS DATE_DE_CONSTRUCTION
FROM structure_souterraine;

Unfortunatly i cannot simply update the error by the correct value because that table is updated every month by another database which is the source of the error. I would have to correct it every month.

What is wrong with my query? Is there another work around ?

Upvotes: 0

Views: 1288

Answers (2)

David Aldridge
David Aldridge

Reputation: 52346

I would think that you'd want:

CREATE OR REPLACE VIEW IC_STRUCTURE("DATE_DE_CONSTRUCTION") AS
SELECT 
  CASE
    WHEN DATE_DE_CONSTRUCTION = '01-AUG-208'
    THEN TO_DATE('01-AUG-2008','DD-MON-YYYY')
    ELSE TO_DATE(DATE_DE_CONSTRUCTION,'DD-MON-YYYY')
  END AS DATE_DE_CONSTRUCTION
FROM structure_souterraine;

Updating the incorrect value and placing a check constraint on:

TO_DATE(DATE_DE_CONSTRUCTION,'DD-MON-YYYY') > date '1900-01-01'

... would seem to be a better fix though.

Upvotes: 2

Art
Art

Reputation: 5782

WHEN DATE_DE_CONSTRUCTION = '01-AUG-208' -- Date or char?
THEN TO_DATE('01-AUG-2008') -- this is a date
ELSE DATE_DE_CONSTRUCTION   -- must be date datatype. Your DATE_DE_CONSTRUCTION most likely char. Convert it to date.

Upvotes: 0

Related Questions