I tested TO_DATE and TO_NUMBER function in TD 14 Express and I have following results:
SELECT TO_NUMBER ('x', '9999.99');
Result is NULL value, OK with documentation "If conversion fails, NULL is returned."
SELECT TO_DATE ('x', 'YYYY-MM-DD');
Result "Failed. 9134: YYYY value must be four digits and in the range 1-9999". Documentation say "If conversion fails, TO_DATE returns NULL.", but query fail. This behavior is pretty same as CAST functions and I don't see any benefit from TO_DATE function.
SELECT TO_TIMESTAMP ('x', 'YYYY-MM-DD HH:MI:SS');
Result same "Failed. 9134: YYYY value must be four digits and in the range 1-9999". Documentation "If the conversion fails, NULL is returned."
Is there any hidden option to push TO_DATE/TO_TIMESTAMP functions to work as needed - never fail and return NULL when wrong date on input?
Teradata Czech Republic
Hi Jan Tomek,
I'm having the same problem you had. I wonder if you got any solutions for it.
My solution for the problem was to build an UDF to check if the date is valid, but when I use it together with other UDFs I got an "Insufficiente Memory" message. Therefore, I'm back on the track looking for a definite solution.
I wonder if there is a way to make this function wok as it was supposed to.