Loading SQL Server TimeStamp Varchar to Teradata Table via TPT

Database
Enthusiast

Loading SQL Server TimeStamp Varchar to Teradata Table via TPT

I have a flat file produced by SQL Sever, and it has a timestamp column as character in format: 2/1/2016 11:54:01 AM -06:00

I have a target temp table to load with: Timestamp(0)

In TPT Description of table, I have: , TaskStartTime timestamp(0)

In the TPT Apply, I have:   ,:TaskStartTime(timestamp(0),format ''YYYY-MM-DDbhh:mi:ss'')

It fails loading:

DATACONNECTION[7]: TPT19108 Data Format 'DELIMITED' requires all 'VARCHAR/JSON/JSON BY NAME/CLOB BY NAME/BLOB BY NAME/XM

L BY NAME/XML/CLOB' schema.

DATACONNECTION[7]: TPT19015 TPT Exit code set to 12.

The error seems off from what I'm doing, but this is the failure. I was able to successully load the flat file if I had the timetamp column in the tem ptable and in the TPT set as VARCHAR(30).

8 REPLIES
Enthusiast

Re: Loading SQL Server TimeStamp Varchar to Teradata Table via TPT

You've got your answer, load it to varchar(30)... the tpt error says it all.  delimited requires ... 

Enthusiast

Re: Loading SQL Server TimeStamp Varchar to Teradata Table via TPT

I thought one of the so-called beauties of using TPT in place of FASTLOAD was that you didn't have to make all columns to load a varchar only, and had to make sure not to load a NULL, etc... but instead other datatypes. Surely we should be able to load a date that is in varchar format and load it into a Teradata Timestamp. It would make life so much easier.

Enthusiast

Re: Loading SQL Server TimeStamp Varchar to Teradata Table via TPT

If I'm stuck with only loading a date timestamp to a varchar(30), then my next question is: how can I convert a Varchar(30) date that looks like:   2/1/2016 11:54:01 AM -06:00     into a Timestamp so I can use easier date logic to extract a date and a time from it?

The alternatives of trying to extact a date and time from a timestamp date that is a varchar string is somewhat nightmarish and becomes a complex endless mountain of cast, substr, trim, index, concat, case, etc...

Junior Supporter

Re: Loading SQL Server TimeStamp Varchar to Teradata Table via TPT

HI.

Your rant is inappropriate. First of all you should have exported the SQL Datetimes in a normalized mode (see ISO 8601), especially if you were going to import them into another, different system.

Secondly, the 'nightmarish' alternatives are as easy as using REGEXP functions properly (you can find a lot of examples to accomplish this).

Cheers.

Carlos.

Junior Contributor

Re: Loading SQL Server TimeStamp Varchar to Teradata Table via TPT

No DBMS/ETL-tool can automagically load dates/timestamps with a non-standard format.

Simply define the column as VARDATE in TPT:

vardate(29) formatin 'mm/dd/yyyyDhh:mi:ssDTDZ' formatout 'YYYY-MM-DDbhh:mi:ss'

And next time to simplify your live follow Carlos' advice to export a correct format...

Enthusiast

Re: Loading SQL Server TimeStamp Varchar to Teradata Table via TPT

Carlos, my apologies if I came off as ranting, clearly I'm frustrated, caused by perhaps doing this to get time:

CAST((CASE WHEN (TRIM(SUBSTR(TRIM(TaskStartTime),INDEX(TaskStartTime,':')-2,2)))<10 THEN '0'||(TRIM(SUBSTR(TRIM(TaskStartTime),INDEX(TaskStartTime,':')-2,2))) ELSE (TRIM(SUBSTR(TRIM(TaskStartTime),INDEX(TaskStartTime,':')-2,2))) End

||TRIM(SUBSTR(TRIM(TaskStartTime),INDEX(TaskStartTime,':')+1,2))

||TRIM(SUBSTR(TRIM(SUBSTR(TRIM(TaskStartTime),INDEX(TaskStartTime,':')+1)),INDEX((SUBSTR(TRIM(TaskStartTime),INDEX(TaskStartTime,':')+1)),':')+1,2))

) AS INTEGER) AS Task_Sec,

TRIM(SUBSTR(TaskStartTime,1, INDEX(TaskStartTime,' ')-1)) AS TDate1,

CASE WHEN CAST((SUBSTR(TRIM(SUBSTR(TaskStartTime,1, INDEX(TaskStartTime,' ')-1)),1,

CASE WHEN SUBSTR(TRIM(SUBSTR(TaskStartTime,1, INDEX(TaskStartTime,' ')-1)),2,1)='/' THEN 1 ELSE 2 End)

) AS INTEGER) <10 THEN '0'||

(SUBSTR(TRIM(SUBSTR(TaskStartTime,1, INDEX(TaskStartTime,' ')-1)),1,

CASE WHEN SUBSTR(TRIM(SUBSTR(TaskStartTime,1, INDEX(TaskStartTime,' ')-1)),2,1)='/' THEN 1 ELSE 2 End)

)ELSE(SUBSTR(TRIM(SUBSTR(TaskStartTime,1, INDEX(TaskStartTime,' ')-1)),1,

CASE WHEN SUBSTR(TRIM(SUBSTR(TaskStartTime,1, INDEX(TaskStartTime,' ')-1)),2,1)='/' THEN 1 ELSE 2 End)

)END AS MM,TRIM(SUBSTR(TaskStartTime,INDEX(TaskStartTime,'/')-2,2)) AS TT,

TRIM(SUBSTR(TRIM(SUBSTR(TaskStartTime,INDEX(TaskStartTime,'/')+1)),INDEX(TRIM(SUBSTR(TaskStartTime,INDEX(TaskStartTime,'/')+1)))+2)) AS TT1,

TaskStartTime

I'm not in control of the extract, though that's good advise, I'm going back to see if I can get the date exported out to a better digestable format for Teradata.

I also did not think about RE, with the above, obviously when there enough substr/index, then RE should be invesitgated.

Thanks,

Enthusiast

Re: Loading SQL Server TimeStamp Varchar to Teradata Table via TPT

Thank you Dieter, if I can't get a timestamp format change to the exported data, I'll use the vardate in/out method you mentioned.

Junior Supporter

Re: Loading SQL Server TimeStamp Varchar to Teradata Table via TPT

SELECT CAST(REGEXP_SUBSTR('2/1/2016 11:54:01 AM -06:00', '(?<= )[0-9]{2}:[0-9]{2}:[0-9]{2} [AP]M [+-][0-9]{2}:[0-9]{2}', 1, 1, 'c') AS TIME(0) WITH TIMEZONE FORMAT 'HH:MI:SSbTbZ') MY_TIME;

MY_TIME
------------------
11:54:01 AM -06:00

HTH.

Cheers.

Carlos.