Fastload DateTime, Timestamp

Tools
N/A

Fastload DateTime, Timestamp

how to insert timestamp in the teradata table from flat file.

the sample data is:
30,01/22/2006 0:00:00
3,01/22/2006 0:00:00

script
------
.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 temp
errorfiles err1,err2;

define
tempid (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
19 REPLIES

Re: Fastload DateTime, Timestamp

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.
N/A

Re: Fastload DateTime, Timestamp

thanx
N/A

Re: Fastload DateTime, Timestamp

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;

Teradata Employee

Re: Fastload DateTime, Timestamp

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
N/A

Re: Fastload DateTime, Timestamp

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.

N/A

Re: Fastload DateTime, Timestamp

nybody ?

Re: Fastload DateTime, Timestamp

anujh:

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

HTH.

Cheers.

Carlos.

N/A

Re: Fastload DateTime, Timestamp

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

Re: Fastload DateTime, Timestamp

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.