Pages

Wednesday, April 5, 2017

String to DATE conversion and validation in 12.2

A new little feature in Oracle Database 12.2 is, that you can convert strings to dates without worrying about exception handling.
(That goes also for converting to numbers or timestamps or other datatypes, but here I'll concentrate on dates.)

It's just one of a ton of new 12.2 features ranging from minor features that make your daily life slightly easier to major inventive features.
I've been part of a Trivadis Team that deep-dived into all of the new features to create the Trivadis TechnoCircle on 12.2 new features and it's been very interesting.

So this is just one new feature, but nice enough for a developer to merit a little blogging about it.
It's actually a double feature - a VALIDATE_CONVERSION function to validate if a conversion can take place, and DEFAULT ON CONVERSION ERROR syntax for TO_DATE to run without raising exceptions for conversion errors.

VALIDATE_CONVERSION

The function returns 1 if the string can be converted according to the format mask:

Note that the default value must be a string that conforms to the format mask.
The default string will always be attempted converted to a date, even if there are no conversion errors.
So if the default string cannot be converted according to format mask, exception is always raised, irrespective of whether there are errors in the actual value to be converted.

'April 5. 2017' default date 'January 1. 4000' on conversion error
*
ERROR at line 2:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0

We might feel it natual to use a DATE for default value, but beware: it is actually implicitly converted to a string, which then afterwards is explicitly converted to a date again (just like if we had entered a default string value.)

This example works only because the session NLS_DATE_FORMAT of 'YYYY.MM.DD' makes the implicit conversion of the DATE return '4000.01.01', which can successfully be converted back to a DATE by the format mask 'YYYY-MM-DD':

But even though it works for this example, it cannot be entirely correct, because if the data would contain '5.4.17' and '4.5.17' it is impossible to know which is April 5th and which is May 4th if both Americans and Europeans can enter data.

VALIDATE_CONVERSION and DEFAULT ON CONVERSION ERROR can be very useful and helpful tools, but you still have to know your data to use the correct format.