CODEWITHSUNDEEP
sqloracle-databasedate
Lokomotywa
Lokomotywa

Reputation: 2844

select invalid date string in oracle

I have a nvarchar2 column in a database table, that stores strings which represent dates, for example '2016-12-05'.

Any way to detect rows that do not contain a valid date string, for example '2012-19-34'?

I would like to have a statements that sets any invalid date string to null.

Upvotes: 2

Views: 2961

Answers (1)

schurik
schurik

Reputation: 7928

you can write a function that try to convert the string into the date:

create function check_date(p_date_str  in nvarchar2) return number as
v_result NUMBER(1,0);
v_date    DATE;
begin 
  v_result := 1;
  begin 
    v_date := to_date( p_date_str, 'YYYY-MM-DD');
  exception  when others then 
   v_result := 0;
  end;
return v_result;  
end;
/

and then use it to find invalid records:

select * from my_table where check_date(column_with_date_as_str) = 0;

Upvotes: 8

Related Questions