UPDATE Teradata Field with NULL value

Database
Fan

UPDATE Teradata Field with NULL value

Hello!

I am having a problem UPDATING Teradata Date fields with a NULL value.

STR_STRING = " Update " & TBLNAME & " set Expiration_Date = '" & Null & "';"

I get an error message: "A Character string failed conversion to a numeric value."

Apparently I’m not using the correct syntax?

5 REPLIES
Enthusiast

Re: UPDATE Teradata Field with NULL value

Hi,

I am not sure & is a concatenation operator in Teradata, I have read only | and !, secondly your string will result in 'NULL' which will be of character data type and cant be stored in date data type.

Khurram
Fan

Re: UPDATE Teradata Field with NULL value

This query is being executed from within EXCEL. The syntax I'm using works fine for any other type of field (data) just having problems UPDATING a date field if I have no value to put in it!

Enthusiast

Re: UPDATE Teradata Field with NULL value

oops, I thaught you are executing it from some sp. but still your value is explicit character 'NULL' which can not be converted to date, as date is stored as numeric. 

Try this:

STR_STRING = " Update " & TBLNAME & " set Expiration_Date = " & Null & ";"

OR

STR_STRING = " Update " & TBLNAME & " set Expiration_Date = CAST('" & Null & "' AS DATE);"
Khurram
Senior Apprentice

Re: UPDATE Teradata Field with NULL value

Instead of a parameter you might try a literal NULL:

STR_STRING = " Update " & TBLNAME & " set Expiration_Date = NULL;"

Dieter

Enthusiast

Re: UPDATE Teradata Field with NULL value

You wont be able to cast null as dates... You need to put NULL eithin your quited string instead of concatrnsting it with the string query, i hope it will work