All Forums UDA
deagle 17 posts Joined 11/08
28 Nov 2008
char to timestamp

Hi,I am new to Teradata but I am already finding timestamps and dates a bit of a headache. My source tableCT source_table (date_modified char(100))I want to insert this field into my destination table CT destination_table (date_modified timestamp(0)).INSERT INTO destination_table SELECT CAST(CAST(date_modified AS TIMESTAMP(0) FORMAT 'DD/MM/YYYYBHH:MI:SS')) AS CHAR(19)) FROM source_tableThis statement returns an error 6760: Invalid timestamp Example data from source table : '28/02/1996 12:34:33'Hope someone can shine a bit of light on this!!!ThanksK

Adeel Chaudhry 773 posts Joined 04/08
28 Nov 2008

Hello,First of all, why are you casting it to VARCHAR to put it in TIMESTAMP(0), after converting it to TIMESTAMP(0)?Second, paranthesis are not ok.Third, following should work, if the column really have all the valid timestamp values means no value which may not be converted to TIMESTAMP(0):SELECT CAST('28/02/1996 12:34:33' AS TIMESTAMP(0) FORMAT 'DD/MM/YYYYBHH:MI:SS');HTH.Regards,Adeel

-- If you are stuck at something .... consider it an opportunity to think anew.

deagle 17 posts Joined 11/08
28 Nov 2008

Hi,Thanks for the quick response. Sorry, was a typo with the ). I was under the impression that you have to cast the format string as a char in queryman, not the case with BTEQ! Again, I am only new to this so will stand corrected.The above is still returning the error 6760: Invalid timestamp.Any more ideas!!

Adeel Chaudhry 773 posts Joined 04/08
28 Nov 2008

Well, about the SQL Assistant and BTEQ check the link below:http://www.teradata.com/teradataforum/Topic13488-9-1.aspx#bm13500Try following:INSERT destination_table (date_modified)SELECT CAST('28/02/1996 12:34:33' AS TIMESTAMP(0) FORMAT 'DD/MM/YYYYBHH:MI:SS');If it works, then you do have any invalid value in your source data, which cannot be converted to valid TIMESTAMP(0). The only way is to dive into the table and check the data itself, or if you can use UDF, use the IS_DATE UDF from following website:http://www.teradata.com/DownloadCenter/Topic9228-137-1.aspxThis UDF will help you filter out all the values which may or may not be converted to TIMESTAMP.Regards,Adeel

-- If you are stuck at something .... consider it an opportunity to think anew.

You must sign in to leave a comment.