With Teradata Studio why does a timestamp(0) export with trailing .0

Teradata Studio

With Teradata Studio why does a timestamp(0) export with trailing .0

Hello,

Our organization has a need to export data internally and for customer deliverables.  When using Teradata Studio Version 14.02.0.201302060329 to export columns with type of  timestamp(0), the resulting data field in the exported file has a trailing ".0".   For example,  a SELECT of the data shows 2013-05-17 08:44:58  but in the export, it appears as  2013-05-17 08:44:58.0.   This was unknown to me at the time, but later, when trying to import this data into a timestamp(0) field, I received an "invalid timestamp" error.  Why is Studio doing this, and is there a resolution to this issue?

The method I am using is as follows:   Right-click on a table in Data Source Explorer, then choose Data -> Teradata Export -> Teradata Export Wizard

Thanks!

-Greg

3 REPLIES
Teradata Employee

Re: With Teradata Studio why does a timestamp(0) export with trailing .0

The '.0' is the result of calling the Java toString() function on a Timestamp value. We will look into resolving this problem when the scale is zero.

Teradata Employee

Re: With Teradata Studio why does a timestamp(0) export with trailing .0

So, in looking into this problem we are not seeing an error (when trying to import this data into a timestamp(0) field, I received an "invalid timestamp" error.) when trying import the same data (with the .0 for timestamp) using our Teradata Import Wizard. We also tried using the basic Load option and did not see the error. What are you using to import the timestamp data back into Teradata?

Re: With Teradata Studio why does a timestamp(0) export with trailing .0

Hello Francine,

Thank you for your response, and looking into this.  I tried to import via Studio, and yes, that worked - thanks.   But, our goal is to export and import high volumes of files and so the GUI is not workable for this situation.   To import, we are using a code generator to generate TPT scripts on the fly, calling TPT to import a delimited file into a staging table of all VARCHAR columns, then finally the code generator invokes a BTEQ insert/select (simplified examples below).   The SELECT is failing with either "invalid timestamp" or "invalid format string".   I'm a Teradata newbie, and cant seem to find the appropriate format string to account for the trailing .0.   Any help would be appreciated.  Thanks!

-Greg

INSERT INTO TARGET_TABLE

SELECT

CAST( MyTimeStamp AS TIMESTAMP(0) format 'YYYY-MM-DDBHH:MI:SS.S(1)')

FROM STAGING_TABLE;

INSERT INTO TARGET_TABLE

SELECT

CAST( MyTimeStamp AS TIMESTAMP(0) format 'YYYY-MM-DDBHH:MI:SS.9(1)')

FROM STAGING_TABLE;

INSERT INTO TARGET_TABLE

SELECT

CAST( MyTimeStamp AS TIMESTAMP(0) format 'YYYY-MM-DDBHH:MI:SS')

FROM STAGING_TABLE;