All Forums Tools
krishna1985 13 posts Joined 08/16
08 Aug 2016
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
 
 
 

Regards, Hari Krishna
krishna1985 13 posts Joined 08/16
09 Aug 2016

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

Regards,
Hari Krishna

Fred 1096 posts Joined 08/04
15 Aug 2016

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.

krishna1985 13 posts Joined 08/16
15 Aug 2016

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

Regards,
Hari Krishna

Fred 1096 posts Joined 08/04
16 Aug 2016

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

You must sign in to leave a comment.