Verify Data Type Conversions: 12 Things Developers Will Love About Oracle Database 12c Release 2 Part 7

It’s one of those all too common problems. Validating a date is indeed a date.

A prime cause of this is the terrible practice of storing dates as strings. One of the biggest issues it is enables people to store things that clearly aren’t dates in “date” columns:

create table dodgy_dates ( 
  id int, 
  is_this_a_date varchar2(20) 
); 
insert into dodgy_dates values (1, 'abc');

Along with a whole bunch of values that might be dates. If you supply the correct format mask:

insert into dodgy_dates values (2, '20150101'); 
insert into dodgy_dates values (3, '01-jan-2016'); 
insert into dodgy_dates values (4, '01/01/2016');

Returning only the valid dates is tricky. If you try to convert everything using to_date(), you’ll get exceptions:

select t.* from dodgy_dates t 
where to_date(is_this_a_date) < sysdate; 
ORA-01858: a non-numeric character was found where a numeric was expected

or maybe:

ORA-01861: literal does not match format string

or

ORA-01843: not a valid month

You could get around this by writing your own is_date() function. Or, if you’re really brave, use a regular expression.

Either way, it’s a lot of unnecessary work.

So to make your life easier, we’ve created a new function, validate_conversion. You pass this a value and a data type. Then Oracle Database will tell you whether it can do the conversion. If it can, it returns one. Otherwise you get zero.

To return the rows in the table that can be real dates, place this in your where clause:

select t.* from dodgy_dates t 
where validate_conversion(is_this_a_date as date) = 1; 
ID         IS_THIS_A_DATE 
---------- -------------------- 
3          01-jan-2016

There’s no error. But where did rows 2 and 4 go? They’re possible dates too!

Validate_conversion only tests one date format at a time. By default this is your NLS_date_format

Each client can set their own format. So if you rely on this, you may get unexpected results…

To avoid this, I strongly recommend you pass the format as a parameter. For example:

select t.* from dodgy_dates t 
where  validate_conversion(is_this_a_date as date, 'yyyymmdd') = 1; 
ID         IS_THIS_A_DATE 
---------- -------------------- 
2          20150101

So to return all the possible dates, you’ll need to call this multiple times:

select t.* from dodgy_dates t 
where validate_conversion(is_this_a_date as date, 'yyyymmdd') = 1 or
  validate_conversion(is_this_a_date as date, 'dd/mm/yyyy') = 1 or 
  validate_conversion(is_this_a_date as date, 'dd-mon-yyyy') = 1; 
ID         IS_THIS_A_DATE 
---------- -------------------- 
2          20150101 
3          01-jan-2016 
4          01/01/2016

And this isn’t just for dates. You can use validate_conversion with any of the following data types:

• binary_double
• binary_float
• date
• interval day to second
• interval year to month
• number
• timestamp
• timestamp with time zone

If you want to convert strings to dates, you’ll need similar logic in the select. This will test the expression against various format masks. If it matches, call to_date with the relevant mask:

case 
  when 
    validate_conversion(is_this_a_date as date, 'yyyymmdd') = 1 
    then to_date(is_this_a_date, 'yyyymmdd') 
  when 
    validate_conversion(is_this_a_date as date, 'dd/mm/yyyy') = 1 
    then to_date(is_this_a_date, 'dd/mm/yyyy') 
  when 
    validate_conversion(is_this_a_date as date, 'dd-mon-yyyy') = 1 
    then to_date(is_this_a_date, 'dd-mon-yyyy') 
end

Full article originally published at blogs.oracle.com on November 10, 2016.