Convert teradat (6) timestamp to datetime for SQL Server

Question

I am working on exporting data from SSIS and I have a truncation error in one of my projects:

[TH27 [91]] Error: "A truncation error has occurred. The column name is" mydate "."

On input (Teradata) I have a type column timestamp(6), and on output in SQL Server I have a type column datetime.

How can I convert it so that when I use SSIS, I don't get this error?

My attempt (query 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 I wrote was at Teradata source here an example of my SSIS scheme

Answer 1

, , SQL Server 6 , Teradata:

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

Answer 2

mydate - , :

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

, teradata , ... :

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

ssis , MSSQL

Answer 3

, SSIS, - , timestamp(6), timestamp(0).

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

Answer 4

(), . ;) , , .

Outside the US, you will soon encounter such errors. Many development systems use settings in the USA that make viewing error messages too easy, but the client system works under local language settings and, in the worst case, on different language settings for the OS and the database. The best way to handle this is to use the internal ISO format (120).

Answer 5

loading data from teradata to sql using SSIS. This is a job for me to convert Timestamp(6)for DateTime for SQL Server

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