CODEWITHSUNDEEP
plsqloracle11g
D1910921
D1910921

Reputation: 11

to_date conversion issue for '00000000'

I have a date field in format of 20140525 (yyyymmdd). I use TO_DATE(20140528,'yyyymmdd') to convert date to 05/28/2014, however this date field also contains '00000000' values which will cause the TO_DATE function to error out due to the invalid month of '00' (and presumably invalid day error as well)

How can I use to_date to convert the valid date formats while ignore the invalid '00000000' values? I still want to retain the dates of '00000000' in the query

Upvotes: 0

Views: 6033

Answers (2)

Gery
Gery

Reputation: 1

TO_DATE(REPLACE(date, '00000000'), 'YYYYMMDD')

Upvotes: 0

Justin Cave
Justin Cave

Reputation: 231751

First, that's why it's a bad idea to store date information in anything other than a date data type. If your data types are correct, life is generally much easier. By storing dates as character strings, you're using more space and getting less value.

If the one and only case where you have non-convertable data is this 00000000 value, you could 

(CASE WHEN column = '00000000' 
      THEN cast(null as date)
      ELSE to_date( column, 'yyyymmdd' )
  END)

In the vast majority of situations, though, once you have an incorrect data type, you're going to end up with more incorrect values. Someone is going to enter a date that doesn't exist (i.e. '20140431') which will also cause your to_date function to fail. To handle that, you'd generally need a function that catches and ignores the exception, i.e.

CREATE OR REPLACE FUNCTION my_to_date( p_str IN VARCHAR2,
                                       p_format IN VARCHAR2 )
  RETURN DATE
IS
BEGIN
  RETURN to_date( p_str, p_format );
EXCEPTION
  WHEN value_error
  THEN
    RETURN null;
END;

Upvotes: 2

Related Questions