Converting teradata timestamp(6) to datetime for SQL Server

Question

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

score 2 · Accepted Answer · answered Sep 01 '17 at 10:20

2

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

answered Sep 01 '17 at 10:20

dnoeth

59,503
4
39
56

JohnHC · Answer 2 · 2017-09-01T09:50:39.380

1

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

edited Sep 01 '17 at 09:50

answered Sep 01 '17 at 09:37

JohnHC

10,935
1
24
40

I got this error SELECT Failed. 3706: Syntax error: Data Type "datetime" does not match a Defined Type name. I executed this request in teradata – Hicham Bouchilkhi Sep 01 '17 at 09:46

score 0 · Answer 3 · answered Sep 01 '17 at 10:14

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

score 0 · Answer 4 · answered Sep 01 '17 at 11:30

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.

score 0 · Answer 5 · edited May 07 '19 at 07:24

0

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

edited May 07 '19 at 07:24

Suraj Rao

29,388
11
94
103

answered May 07 '19 at 07:16

Kamesh Karkhur

1

Converting teradata timestamp(6) to datetime for SQL Server

5 Answers5