Casting Date in Bteq utilities

Tools
Enthusiast

Casting Date in Bteq utilities

Hi Team,

I am new to Teradata  and I have a requirement to load the data FROM EXCEL using BTEQ UTILITIY. However as far as I know we cannot load data from excel but can load in CSV format.

requirement is as below

I have to load the set of records and below is the source data ie CSV

ID                DATE

234567        01AUG2016 5:57:51

Target table in TD which we have is

create set table xx

(

ID

date timestamp(6)

)

PI(ID)

I tried writing the BTEQ Script ie

using(

ID

Date --------how to convert 01AUG2016 5:57:51 TO timestamp (6) in Teradata ??????

)

insert into xx

values (

:id

:date

);

.quit

.logoff

4 REPLIES
Enthusiast

Re: Casting Date in Bteq utilities

Hi Team,

can anyone let me know syntax to convert date as above in fastload instead of bteq plzz

Teradata Employee

Re: Casting Date in Bteq utilities

FastLoad supports only limited data type conversions, either implicit or using Teradata-style syntax. And at a minimum you would need to change the input to always have 2 digits for the hour. If you are saving from Excel as CSV, apply a custom format to the column to make it compatible with Teradata / ANSI standard: yyyy-mm-dd hh:mm:ss

Also if the input is delimited text, all the fields in your FastLoad DEFINE must be VARCHAR.

Enthusiast

Re: Casting Date in Bteq utilities

Hi Fred,

thank you very much for your inputs howver I have tried to create the staging table with the below query and used oTranslate funcion to pad 0 if its single digit 

substring( DIARY_DATE  from 6 for 4)|| '-'||  SUBSTRING(DIARY_DATE  from 3 for 3)||'-'|| SUBSTRING(DIARY_DATE  from 1 for 2)||' ' || oTRANSLATE(SUBSTRING('0' from 1 for 18-length(DIARY_DATE) )||

SUBSTRING(DIARY_DATE from 10 for 10),   ' ','')

Teradata Employee

Re: Casting Date in Bteq utilities

Not sure I understand your question at this point. Are you saying that you loaded to a staging table as character, and now you want a SQL expression that will CAST the character string to a Timestamp, such as:

CAST(

CASE WHEN SUBSTRING(DIARY_DATE FROM 12 for 1) = ':'

THEN SUBSTRING(DIARY_DATE from 1 for 10)||'0'||SUBSTRING(DIARY_DATE from 11 for 7)

ELSE DIARY_DATE END

AS TIMESTAMP FORMAT'ddmmmyyyy hh:mm:ss')