0

I have a field (of varchar2 type) which holds date. How can I check if it is in 'MM/DD/RRRR' format?

Aniket Ghosh
  • 1
  • 2
  • 1
    Possible duplicate of [Oracle using REGEXP to validate a date field](https://stackoverflow.com/questions/15736503/oracle-using-regexp-to-validate-a-date-field) – Ronnis Jul 20 '18 at 07:04
  • Question was asked already several times, however with Oracle 12.2 you get a built-in solution. – Wernfried Domscheit Jul 20 '18 at 08:05

2 Answers2

1

In Oracle 12.2 you can use the built-in VALIDATE_CONVERSION function:

... WHERE VALIDATE_CONVERSION(col AS DATE, 'MM/DD/YYYY') = 1

Or you can use TO_DATE function where you can specify a default value on conversion error:

TO_DATE(col DEFAULT '01/01/1900' ON CONVERSION ERROR, 'MM/DD/YYYY')
Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110
0

One option is to create a function which converts that string into a date. If it fails, then it is invalid.

For example:

SQL> create or replace function f_check_date (par_datum in varchar2)
  2    return varchar2
  3  is
  4    l_date date;
  5  begin
  6    l_date := to_date(par_datum, 'mm/dd/rrrr');
  7    return ('Valid');
  8  exception
  9    when others then
 10      return ('Invalid');
 11  end;
 12  /

Function created.

Testing:

SQL> with test (datum) as
  2    (select '05/18/2018' from dual union
  3     select '99/98/7655' from dual union
  4     select '01/02/3456' from dual union
  5     select '13/22/1000' from dual
  6    )
  7  select datum, f_check_date(datum) is_valid
  8  from test;

DATUM      IS_VALID
---------- --------------------
01/02/3456 Valid
05/18/2018 Valid
13/22/1000 Invalid
99/98/7655 Invalid

SQL>

Conclusion: don't store dates in any other datatype column but DATE.

Littlefoot
  • 131,892
  • 15
  • 35
  • 57