Tags
CAST, Data Conversion, ORACLE, ORACLE 12c Release 2, SQL, TO_CHAR, TO_DATE, TO_NUMBER
ORACLE 12.2 has a new option to handle conversion errors when using functions like CAST, TO_NUMBER, TO_CHAR, TO_DATE, etc. Using this option we may avoid conversion errors an return a default value instead.
The examples use TO_DATE but the option is useable in the other conversion functions accordingly
EXAMPLE TO_DATE
In case of a string to date conversion error we would like to return the 1st of January of 2016.
WITH data (str) AS (SELECT '15.01.2016' FROM dual UNION ALL
SELECT '30.06.2016' FROM dual UNION ALL
SELECT '30.02.2016' FROM dual UNION ALL
SELECT 'gugus' FROM dual UNION ALL
SELECT '07.03.2016' FROM dual UNION ALL
SELECT '15.10.2016' FROM dual)
SELECT str
, TO_DATE(str DEFAULT '01.01.2016' ON CONVERSION ERROR,'DD.MM.YYYY') AS conversion
FROM data
/
STR CONVERTED
---------- ----------
15.01.2016 15.01.2016
30.06.2016 30.06.2016
30.02.2016 01.01.2016
gugus 01.01.2016
07.03.2016 07.03.2016
15.10.2016 15.10.2016
6 rows selected.
Using SYSDATE as default
Using SYSDATE as the default value instead of a literal value works as expected.
WITH data (str) AS (SELECT '15.01.2016' FROM dual UNION ALL
SELECT '30.06.2016' FROM dual UNION ALL
SELECT '30.02.2016' FROM dual UNION ALL
SELECT 'gugus' FROM dual UNION ALL
SELECT '07.03.2016' FROM dual UNION ALL
SELECT '15.10.2016' FROM dual)
SELECT str
, TO_DATE(str DEFAULT SYSDATE ON CONVERSION ERROR,'DD.MM.YYYY') AS converted
FROM data
/
STR CONVERTED
---------- ----------
15.01.2016 15.01.2016
30.06.2016 30.06.2016
30.02.2016 30.09.2016
gugus 30.09.2016
07.03.2016 07.03.2016
15.10.2016 15.10.2016
6 rows selected.
Default to start of the year…
So next step would be to invoke a function on sysdate, to get the first day of the current year as the default value for an occuring conversion error.
WITH data (str) AS (SELECT '15.01.2016' FROM dual UNION ALL
SELECT '30.06.2016' FROM dual UNION ALL
SELECT '30.02.2016' FROM dual UNION ALL
SELECT 'gugus' FROM dual UNION ALL
SELECT '07.03.2016' FROM dual UNION ALL
SELECT '15.10.2016' FROM dual)
SELECT str
, TO_DATE(str DEFAULT TRUNC(SYSDATE,'YEAR') ON CONVERSION ERROR,'DD.MM.YYYY') AS converted
FROM data
/
Error at Command Line : 8 Column : 28
Error report -
SQL Error: ORA-43907: This argument must be a literal or bind variable.
So, I am a little bit puzzled…we can use SYSDATE but not having a function around it…
Choosing another column value as the default
Now, how about choosing another column of the same table as the default value?
WITH data (str,dateval) AS (SELECT '15.01.2016', date '2015-07-15' FROM dual UNION ALL
SELECT '30.06.2016', date '2015-07-16' FROM dual UNION ALL
SELECT '30.02.2016', date '2015-07-17' FROM dual UNION ALL
SELECT 'gugus' , date '2015-07-18' FROM dual UNION ALL
SELECT '07.03.2016', date '2015-07-19' FROM dual UNION ALL
SELECT '15.10.2016', date '2015-07-20' FROM dual)
SELECT str
, dateval
, TO_DATE(str DEFAULT dateval ON CONVERSION ERROR,'DD.MM.YYYY') AS converted
FROM data
/
Error at Command Line : 9 Column : 29
Error report -
SQL Error: ORA-43907: This argument must be a literal or bind variable.
Ok, perhaps it needs to be a string using the same format mask?
WITH data (str,dateval) AS (SELECT '15.01.2016', date '2015-07-15' FROM dual UNION ALL
SELECT '30.06.2016', date '2015-07-16' FROM dual UNION ALL
SELECT '30.02.2016', date '2015-07-17' FROM dual UNION ALL
SELECT 'gugus' , date '2015-07-18' FROM dual UNION ALL
SELECT '07.03.2016', date '2015-07-19' FROM dual UNION ALL
SELECT '15.10.2016', date '2015-07-20' FROM dual)
SELECT str
, dateval
, TO_DATE(str DEFAULT TO_CHAR(dateval,'DD.MM.YYYY') ON CONVERSION ERROR,'DD.MM.YYYY') AS converted
FROM data
/
Error at Command Line : 9 Column : 29
Error report -
SQL Error: ORA-43907: This argument must be a literal or bind variable.
So, how can this be solved?
Now, we could return NULL in case of a conversion error and then replace a null value by the value of the second column…like this:
WITH data (str,dateval) AS (SELECT '15.01.2016', date '2015-07-15' FROM dual UNION ALL SELECT '30.06.2016', date '2015-07-16' FROM dual UNION ALL SELECT '30.02.2016', date '2015-07-17' FROM dual UNION ALL SELECT 'gugus' , date '2015-07-18' FROM dual UNION ALL SELECT '07.03.2016', date '2015-07-19' FROM dual UNION ALL SELECT '15.10.2016', date '2015-07-20' FROM dual UNION ALL SELECT NULL , date '2015-07-21' FROM DUAL) SELECT str , dateval , NVL(TO_DATE(str DEFAULT NULL ON CONVERSION ERROR,'DD.MM.YYYY'),dateval) AS converted FROM data / STR DATEVAL CONVERTED ---------- ---------- ---------- 15.01.2016 15.07.2015 15.01.2016 30.06.2016 16.07.2015 30.06.2016 30.02.2016 17.07.2015 17.07.2015 gugus 18.07.2015 18.07.2015 07.03.2016 19.07.2015 07.03.2016 15.10.2016 20.07.2015 15.10.2016 21.07.2015 21.07.2015 7 rows selected.
but…I only wanted to replace values that are not convertable by the second value, not null values…
WITH data (str,dateval) AS (SELECT '15.01.2016', date '2015-07-15' FROM dual UNION ALL SELECT '30.06.2016', date '2015-07-16' FROM dual UNION ALL SELECT '30.02.2016', date '2015-07-17' FROM dual UNION ALL SELECT 'gugus' , date '2015-07-18' FROM dual UNION ALL SELECT '07.03.2016', date '2015-07-19' FROM dual UNION ALL SELECT '15.10.2016', date '2015-07-20' FROM dual UNION ALL SELECT NULL , date '2015-07-21' FROM DUAL) SELECT str , dateval , CASE WHEN str IS NULL THEN NULL ELSE NVL(TO_DATE(str DEFAULT NULL ON CONVERSION ERROR,'DD.MM.YYYY'),dateval) END AS converted FROM data /
A little bit complicated, but this way I get what I want.
Handling different date formats in one query
Now another test case, having an input stream using different date formats…
WITH data (str) AS (SELECT '15.03.2016' FROM dual UNION ALL
SELECT '2005/03/15' FROM dual UNION ALL
SELECT '15-MRZ-2016' FROM dual UNION ALL
SELECT '15-MAR-2016' FROM dual UNION ALL
SELECT '15. MARCH 2016' FROM dual UNION ALL
SELECT '15-MAALIS-2016' FROM DUAL UNION ALL
SELECT '15-BRE-2016' FROM DUAL)
SELECT str
, COALESCE(TO_DATE(str DEFAULT NULL ON CONVERSION ERROR,'DD.MM.YYYY')
,TO_DATE(str DEFAULT NULL ON CONVERSION ERROR,'YYYY/MM/DD')
,TO_DATE(str DEFAULT NULL ON CONVERSION ERROR,'DD-MON-YYYY','NLS_DATE_LANGUAGE=ENGLISH')
,TO_DATE(str DEFAULT NULL ON CONVERSION ERROR,'DD-MON-YYYY','NLS_DATE_LANGUAGE=GERMAN')
,TO_DATE(str DEFAULT NULL ON CONVERSION ERROR,'DD-MON-YYYY','NLS_DATE_LANGUAGE=FINNISH')
) AS converted
FROM data
/
STR CONVERTED
-------------- ----------
15.03.2016 15.03.2016
2005/03/15 15.03.2005
15-MRZ-2016 15.03.2016
15-MAR-2016 15.03.2016
15. MARCH 2016 15.03.2016
15-MAALIS-2016 15.03.2016
15-BRE-2016
7 rows selected.
Conclusion
The DEFAULT … ON CONVERSION ERROR is a very powerful feature, which will help us solving conversion errors very elegant. Of course we will still not be able to do conversions when the input value is not unambiguous (A string value of ‘03.05.2016’ could be the 3rd of May as well as the 5th of March) but still a very welcome feature.