CODEWITHSUNDEEP
sqlsql-serverssisteradata
Hicham Bouchikhi
Hicham Bouchikhi

Reputation: 726

Converting teradata timestamp(6) to datetime for SQL Server

I'm working on a data exportation from SSIS, and I have a truncation error in one of my project:

[TH27 [91]] Error: "A truncation error occurred. Column name is 'mydate'."

In the input (Teradata), I have a column of type timestamp(6), and in the output in SQL Server, I have a column of type datetime.

How can I convert it in way that when I use SSIS I don't get this kind of error?

My attempt (request 1):

SELECT 
    column1,
    CAST(CAST(CAST(mydate AS DATE FORMAT 'YYYY-MM-DD') AS CHAR(10)) || ' ' 
             || TRIM(EXTRACT(HOUR FROM (mydate))) || ':'
             || TRIM(EXTRACT(MINUTE FROM (mydate))) || ':'
             || TRIM(CAST(EXTRACT(SECOND FROM (mydate)) AS INTEGER)) AS Date) AS mydate,
    column2
FROM table1

Update:

The request that I wrote was in the Teradata source here's an example of my SSIS schema

My SSIS Schema

Upvotes: 5

Views: 6674

Answers (5)

Kamesh Karkhur
Kamesh Karkhur

Reputation: 1

loading data from teradata to sql with the help of SSIS. This is work for me convert Timestamp(6) for datetime for sql server 

To_Char(myDate,'yyyy-mm-dd hh:mi:ss.ff3')

Upvotes: 0

crumble
crumble

Reputation: 66

Don't use cast on date(time), use convert. This will work only on your machine ;) As soon as you have to deal with multiple culture settings, this may fail because different string representations of the date format.

Outside the USA you will run very soon into such errors. Many development systems use US settings too ease browsing for error messages, but the customers system runs under the local language settings and at worst on different language settings for OS and DB. The best way to handle this, is to use the ISO format (120) internally.

Upvotes: 0

dnoeth
dnoeth

Reputation: 60502

It's probably because SQL Server doesn't support 6 fractional digits, so cast it to a string with 3 fractional digits on Teradata:

To_Char(myDate,'yyyy-mm-dd hh:mi:ss.ff3')

Upvotes: 2

Hicham Bouchikhi
Hicham Bouchikhi

Reputation: 726

I found this solution, that work for me in SSIS is to remove the last part that we have in timestamp(6) and convert it after that in timestamp(0).

select column1,
       CAST(SUBSTRING(CAST(mydate AS CHAR(26)) FROM 1 FOR 19) AS TIMESTAMP(0))
       as mydate 
from MyTable

Upvotes: 0

JohnHC
JohnHC

Reputation: 11205

If mydate is timestamp, just cast it:

select column1,
       cast(mydate as datetime) as column2
from MyTable

Ok, so executing in teradata means you don't have datetime... But you do have date and time:

select column1,
       cast(mydate as date) as column2date,
       cast(mydate as time) as column2time
from MyTable

You can then use ssis to manipulate the data to combine the date and time into an MSSQL datetime

Upvotes: 1

Related Questions