All Forums Tools
suhel 4 posts Joined 05/07
09 Jun 2007
Fastload DateTime, Timestamp

how to insert timestamp in the teradata table from flat file.the sample data is:30,01/22/2006 0:00:003,01/22/2006 0:00:00script------.logon mytd/myuser,myuser;drop table temp;drop table err1;drop table err2;create table temp(tempid numeric,birthdate date);set record vartext ",";begin loading temperrorfiles err1,err2;definetempid (varchar(3)),birthdate (varchar(19),Nullif='0000-00-00b00:00:00'),newlinechar (varchar(2))FILE = C:\temp.txt;insert into temp(tempid,birthdate)values(:tempid,:birthdate(format 'yyyy-mm-ddbhh:mm:ss'));end loading;.logoff;-------------------------------------- --------------------the above code gives an error "invalid format string"I want the Output as it is in 'Source data' for the timestamp field.plz help!!Thank you

Barry-1604 176 posts Joined 07/05
11 Jun 2007

It looks like you have a few issues:1) Your data is in a format of 'mm/dd/yyyybhh:mi:ss' but your format has 'yyyy-mm-ddbhh:mm:ss'. Your "nullif" statement is also in a format that doesn't match your data.2) You only have one digit specified in your data for the hour, but you need to have two digits specified.3) The proper format to indicate minutes is "mi", not "mm". So, your format should be 'mm/dd/yyyybhh:mi:ss'.The reason you are getting the error is #3 above, but you'll hit the other things shortly after if you don't correct them.Hope that helps.

suhel 4 posts Joined 05/07
11 Jun 2007

thanx

anujh 22 posts Joined 05/11
07 Nov 2011

This doesnt seem to work , is it even possible.. I have tried with both having b for blank space in the format..but still the records got rejected

data

30,2011-01-01 00:00:00
40,2011-11-21 00:00:00
 

fastload script

sessions 20;
errlimit 10225;
sleep 6;
tenacity 1;
.logon

DATABASE d_eiadb ;
drop table temp;
drop table err1;
drop table err2;

create table temp
(
tempid numeric,
birthdate date
);

set record vartext ",";

begin loading temp
errorfiles err1,err2;

define
tempid (varchar(3)),
birthdate (varchar(19),Nullif='0000-00-00 00:00:00')
FILE = data.txt;

insert into temp(tempid,birthdate)
values
(:tempid,:birthdate(format 'YYYY-MM-DDbHH:MI:SS'));

end loading;

.logoff;

feinholz 1234 posts Joined 05/08
07 Nov 2011

What I see if a table with 2 columns:

create table temp
(
tempid numeric,
birthdate date
);

 

The data you have is not valie for a "DATE" column.

The data you have looks more like a TIMESTAMP than a DATE.

And "numeric"?

What Teradata data type is that?

In future posts, when you say that the rows get rejected, then it would be most helpful if you could include any output and/or error messages.

--SteveF

anujh 22 posts Joined 05/11
14 Nov 2011

The Data that i have in the file is ttimestamp and I want to load into the date colum..

thats what i thought this thread was about..

 

With regards to numeric, i just picked the defination from the above post and the table is created within teradata without any issues.

output of show table...

CREATE SET TABLE temp ,NO FALLBACK ,

NO BEFORE JOURNAL,

NO AFTER JOURNAL,

CHECKSUM = DEFAULT

(

tempid DECIMAL(5,0),

birthdate DATE FORMAT 'YYYY-MM-DD')

PRIMARY INDEX ( tempid );

 

tables are being loaded into error table

 ErrorCode ErrorFieldName DataParcel
1 2665 BIRTHDATE DataParcel001.dat
2 2665 BIRTHDATE DataParcel002.dat
 

error being Invalid date.

I thought that there was an Impicit conversion from Timestamp to date... atleast that seems  to work when inserting data from one table to another.

anujh 22 posts Joined 05/11
30 Nov 2011

nybody ?

CarlosAL 512 posts Joined 04/08
30 Nov 2011

anujh:

The (not) implicit conversion you're talking about is from VARCHAR to DATE, not from TIMESTAMP to DATE (implicit).

HTH.

Cheers.

Carlos.

dnoeth 4628 posts Joined 11/04
30 Nov 2011

You can't load a VarChar representing a Timestamp into a Date column using FastLoad. This would require two typecasts and only one is supported.

Of course you could use MLoad or simply try to export that timestamp as a date.

Dieter

Dieter

CarlosAL 512 posts Joined 04/08
30 Nov 2011

Hi:

Sorry if I didn't make myself clear. This is what I meant. There is not implicit conversion between VARCHAR (formatted as a 'TIMESTAMP') and a DATE (but there is between a TIMESTAMP and a DATE).

Nontheless, if you can manage to 'fix' the file you could load it into a table without explicit casting:

The file (note the 'timestamp' format): 

30,2006-01-22 00:00:00
3,2006-01-22 00:00:00

The fastload script (note the timestamp(0) birthdate column)

.logon mytd/myuser,myuser;
drop table temp;
drop table temperr1;
drop table temperr2;
create table temp
(
tempid numeric,
birthdate timestamp(0)
);

set record vartext ",";

begin loading temp
errorfiles temperr1,temperr2;

define
tempid (varchar(3)),
birthdate (varchar(19))
FILE = C:\temp.txt;

insert into temp(tempid,birthdate)
values
(:tempid,:birthdate);

end loading;
 

The data:

 BTEQ -- Enter your SQL request or BTEQ command:
select * from temp;

 *** Query completed. 2 rows found. 2 columns returned.
 *** Total elapsed time was 1 second.

 tempid            birthdate
-------  -------------------
    30.  2006-01-22 00:00:00
     3.  2006-01-22 00:00:00

 

HTH.

Cheers.

Carlos.

feinholz 1234 posts Joined 05/08
30 Nov 2011

What you just showed was loading timestamp data into a timestamp column.

However you cannot load data from a timestamp value into a DATE column in Teradata using FastLoad.

Teradata will not do the conversion (and FastLoad does not convert data).

 

--SteveF

anujh 22 posts Joined 05/11
30 Nov 2011

Thanks everone, as a workaround I used multiload and use substring to get the date.

 

CarlosAL 512 posts Joined 04/08
01 Dec 2011

Steven:

I respectfully disagree. The example showed the loading of the 'text representation' of a timestamp. Teradata stores timestamps as structures which are not 'text' (this makes possible the implicit casting that the poster was referring to in the INSERT...SELECTS and the like)

I wanted to point out the difference between implicit conversions (timestamp to dates) that cannot be done from texts formatted as timestamps to dates.

Cheers.

Carlos.

ZackHowe 15 posts Joined 07/06
26 Apr 2012

How about throw away the time portion with a unused field?

data

30,2011-01-01 00:00:00
40,2011-11-21 00:00:00
 

fastload script

sessions 20;
errlimit 10225;
sleep 6;
tenacity 1;
.logon

DATABASE d_eiadb ;
drop table temp;
drop table err1;
drop table err2;

create table temp
(
tempid int,
birthdate date
);

set record vartext ",";

begin loading temp
errorfiles err1,err2;

define
tempid (varchar(3)),
birthdate (varchar(10),Nullif='0000-00-00')

filler (varchar(9))
FILE = data.txt;

insert into temp(tempid,birthdate)
values
(:tempid,:birthdate(format 'YYYY-MM-DD'));

end loading;

.logoff;

feinholz 1234 posts Joined 05/08
27 Apr 2012

That NULLIF example will not work.

The NULLIF feature says "NULL the column if the value of the field is equal to the value in the NULLIF clause".

The NULLIF feature cannot do substrings.

Thus, that NULLIF clause would never equate to TRUE with the given data.

 

--SteveF

feinholz 1234 posts Joined 05/08
27 Apr 2012

Carlos, I am not sure to what you are disagreeing.

With the data presented as VARCHAR (delimited data is read in as a series of VARCHAR fields), FastLoad would create a USING clause on the INSERT statement with that VARCHAR column.

Trying to load that VARCHAR data (yes, it is an external representation of a timestamp) into a DATE field will not work.

The example you showed loaded the data into a TIMESTAMP column and that is why it worked.

Teradata will not allow a character string representing a timestamp value to be loaded into a DATE column.

 

--SteveF

mathew 1 post Joined 05/12
04 May 2012

HI, I am facing a similar issue.

 

I am trying to write the output of the below query to a file, but it is coming with the header.

 SELECT LAST_UPDATE_DATE FROM DB.tablename

 

I tried using (ttitle'') also, but it is not working.

 SELECT LAST_UPDATE_DATE (trim '')FROM DB.tablename

 

The columns which I am selecting is a time stamp column. Can anyone help?

Thanks,

Mathew

 

 

s@ir@m 35 posts Joined 05/13
04 Sep 2013

Hi Experts,
i have one table data like columns (id,date)
insert into data(100,current_date);
i.e date is 04-Aug-2013  ' how it converts the date as teradata calculate the date .
 
plz provide me the solution ?
 
Thanks
Ratnam

ThomasNguyen 30 posts Joined 04/09
10 Sep 2013

You can refer to the DEFINE command in the FastLoad Reference Manual,
Item "Using ANSI/SQL DateTime Data Types" and "Table 30: ANSI/SQL DateTime Specifications"
Thomas

s@ir@m 35 posts Joined 05/13
17 Oct 2013

thq

You must sign in to leave a comment.