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))

Advertisements
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:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s