Seems wrong TO_DATE/TO_TIMESTAMP functions in Teradata 14

Database
Teradata Employee

Seems wrong TO_DATE/TO_TIMESTAMP functions in Teradata 14

Hello, 

   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?

Thank you

Jan Tomek

jan.tomek@teradata.com

Teradata Czech Republic

1 REPLY

Re: Seems wrong TO_DATE/TO_TIMESTAMP functions in Teradata 14

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.

Diego