char to timestamp

UDA
Enthusiast

char to timestamp

Hi,

I am new to Teradata but I am already finding timestamps and dates a bit of a headache.

My source table

CT 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_table

This 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!!!

Thanks
K
3 REPLIES
Teradata Employee

Re: char to timestamp

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
Enthusiast

Re: char to timestamp

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!!
Teradata Employee

Re: char to timestamp

Well, about the SQL Assistant and BTEQ check the link below:

http://www.teradata.com/teradataforum/Topic13488-9-1.aspx#bm13500

Try 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.aspx

This UDF will help you filter out all the values which may or may not be converted to TIMESTAMP.

Regards,

Adeel