All Forums Database
dsreddy117 20 posts Joined 11/07
18 Nov 2007
Inserting current_timestamp value into timestamp(0) column

I have a tablecreate table stats( record_time TIMESTAMP(0) )For every load I need to populate the current_timestamp value into this column.I tried withinsert into stats (record_time )selectcurrent_timestampI 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.

nithyanandam 65 posts Joined 10/04
19 Nov 2007

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

dsreddy117 20 posts Joined 11/07
19 Nov 2007

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.

Someshnr 53 posts Joined 06/07
19 Nov 2007

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

Someshnr 53 posts Joined 06/07
19 Nov 2007

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

Fred 1096 posts Joined 08/04
19 Nov 2007

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"

dsreddy117 20 posts Joined 11/07
20 Nov 2007

Thanks Fred.My problem solved with your answer :-)Somesh, even your solution is working fine.

You must sign in to leave a comment.