Converting from Teradata Timestamp to SQL Server Datetime ‏

Teradata has a timestamp data type that appears to be equivalent to a SQL Server datetime.  It is not. Attempting to use the SSIS Data Conversion task will likely fail with a truncation error. 

While the solution below is extremely slow, it does work between  Teradata 14.10 and SQL Server 2008 R2 and SQL Server 2012.  It will still require a SSIS Data Conversion task to take the output and convert it to a DT_DBTIMESTAMP.

select CAST(CAST(<teradata_column> AS DATE FORMAT ‘YYYY-MM-DD’) AS CHAR(10))  || ‘ ‘ || TRIM(EXTRACT(HOUR FROM (<teradata_column>))) || ‘:’|| TRIM(EXTRACT(MINUTE FROM (<teradata_column>))) || ‘:’ || TRIM(CAST(EXTRACT(SECOND FROM (<teradata_column>)) AS INTEGER))

This entry was posted in SQL Server Integration Services, Teradata and tagged , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s