Fastload Varchar to Timestamp

Database
tty
Enthusiast

Fastload Varchar to Timestamp

Hi,

I'm writing a fastload to load from a comma delimited file. I'm using SET RECORD VARTEXT in the script to load from the file.

The loading returns an error when it attempts to load into a timestamp(0) column in the new table.

The source timestamp data looks like this : 5/16/2005 9:44:44 PM. How do I convert this to timestamp for it to be loaded into the new table?

An extract of my fastload script looks like this:

...
SET RECORD VARTEXT ",";
...
CREAT TABLE MyTable (
MyTimestamp TIMESTAMP(0)
...
)
...

DEFINE
in_MyTimestamp (VARCHAR(255))
...

INSERT INTO MyNewTable VALUES(
:in_MyTimestamp (FORMAT 'someformat...')
...
)

Any help appreciated! Thanks!
TTY

3 REPLIES
Senior Apprentice

Re: Fastload Varchar to Timestamp

Hi tty,
a cast using a format string will only work, if it's always 2 digits for day/month/hour like
select '05/16/2005 09:44:44 PM' (timestamp, format 'mm/dd/yyyyBhh:mi:ssBT')

If you can't reformat the source data you'll probably need an INMOD.

Dieter
tty
Enthusiast

Re: Fastload Varchar to Timestamp

Hi dnoeth,

Thanks for the tip! Seems like changing the source will be my best option for now. Thanks again.

Ty
Enthusiast

Re: Fastload Varchar to Timestamp

there should be some method of formating the timestamp column at source to make compatible to the teradata timestamp column
i mean in my flat file the data is coming like dd-mm-yyyy 00:00:00 but fastload fails with timestamp error

I tried putting the
in the insert statemnt (TIMESTAMP(6), FORMAT 'dd-mm-yyyyBHH:MI:SS')
but with this also it is not working

m i using the coorect format in as above mentioned