Inserting current_timestamp value into timestamp(0) column

Database
Enthusiast

Inserting current_timestamp value into timestamp(0) column

I have a table

create table stats
(
record_time TIMESTAMP(0)
)

For every load I need to populate the current_timestamp value into this column.

I tried with

insert into stats
(
record_time
)
select
current_timestamp

I am getting the error
7454: Date Time field overflow.

Can you please help me with the above insert statement. I tried CAST options, but it's not working.
6 REPLIES
Enthusiast

Re: Inserting current_timestamp value into timestamp(0) column

To insert current timestamp for any column, just use current_timestamp. You don't have to select current_timestamp inside an insert statement.
Enthusiast

Re: Inserting current_timestamp value into timestamp(0) column

Thanks for th advise. My problem is I can't insert the the current_timestamp in timestamp(0) column.

How to cast the current_timestamp to fit into the timestamp(0) data type?

Just to give better idea, I need system date in the 'yyyy-mm-dd hh:mi:ss' format in that column.

Enthusiast

Re: Inserting current_timestamp value into timestamp(0) column

Data type for CURRENT_TIMESTAMP is TIMESTAMP(6) and you are trying to insert a TIMESTAMP(6) data to a TIMESTAMP(0) and that's why you are getting this error. Either change the data type for table definition to Timestamp(6) or handle datatype in your select query to Timestamp(0).
Enthusiast

Re: Inserting current_timestamp value into timestamp(0) column

You can use this expression in the select part...

Sel
cast(CURRENT_DATE as TimeStamp(0))
+ ((CURRENT_TIME - time '00:00:00') hour to second(0))

Teradata Employee

Re: Inserting current_timestamp value into timestamp(0) column

CURRENT_TIMESTAMP has datatype "TIMESTAMP(6) WITH TIME ZONE".
Teradata is willing to ignore the timezone offset in an assignment, but not to truncate nonzero fractional seconds. So just use CURRENT_TIMESTAMP(0) which is "TIMESTAMP(0) WITH TIME ZONE"
Enthusiast

Re: Inserting current_timestamp value into timestamp(0) column

Thanks Fred.

My problem solved with your answer :-)

Somesh, even your solution is working fine.