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.

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;

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.

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.

nybody ?

anujh:

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

HTH.

Cheers.

Carlos.

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

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.

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

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

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.

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;

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.

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.

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