Insert Into Teradata

[skodali]

Hello All,

I’m trying to insert values into a table, receiving an error “Datetime field overflow”.
I’m not sure why the error message showing ‘?’ instead of the values which I’m passing.
I have 4 columns with date values when I remove these date fields and try to insert then it is successfully inserting the values into the table.
For the reference attached the mapper and the error message.
Can anyone help me with this?

Thanks,
Sasank

[stephenknilans]

What is the PRECISE definition in teradata, and in snaplogic? There are at least 2 types of timestamps in teradata, and they are looking for relevant data for every byte. Make one mistake, and it will reject the data.

|TIMESTAMP (0)
CHAR (19)
Timestamp(0) is YYYY-MM-DDbHH:MI:SS

TIMESTAMP (6)
CHAR (26)
Timestamp(6) is YYYY-MM-DDbHH:MI:SS.ssssss (milliseconds extra)

https://docs.teradata.com/reader/WurHmDcDf31smikPbo9Mcw/VgEeisUpvM6NNgAXNLdJzQ

You could also try explicitly casting: sel cast('2008-10-23’as date format ‘yyyy-mm-dd’) (TIMESTAMP, FORMAT ‘YYY
Y-MM-DD-HH:MI:SS.S(6)’) to see what you should do.

[skodali]

@stephenknilans Thanks for the response.
INSERT INTO dwh_manage.job_instances
( job_identifier
,started_at
,stopped_at
,last_modified_from
,last_modified_before
,source_name
,return_code
,return_message

)
VALUES
( ‘netsuite_pull_postings’
,CURRENT_TIMESTAMP(3)
,CURRENT_TIMESTAMP(3)
,< posting date >
,< posting date > + INTERVAL ‘1’ DAY
,‘UK_DEFERRED’
,0
,‘end’
); This it the exact query used to insert data into teradata. The [posting date] format can be just the date <2019-10-16>. Trying to insert current timestamp(3).

If possible can you please provide an example where you have tried to insert date fields into Teradata through Snaplogic?

[stephenknilans]

I have only done it with a competing product, and various languages and utilities. I was actually on a contract where the SAME table had fields defined about 5 different ways. Luckily, I got the DBA to change the format to be consistent. But NONE would accept data unless it was null, or an appropriate value. So I had the same type of problem.

Also, now that I think about it, at one point they upgraded that product(That has the schema definition in the source/target objects), and the company changed the timestamp format, and I had to change the format in my code to suit. The format was IDENTICAL, but one didn’t have microseconds, and the other did. If all of your errors are overflows, THAT might be your problem! Is the last part SS, SS.sss, or SS.ssssss?