Menu
When using this statement
create table demo ( ts timestamp ) insert into demo select current_timestamp I get the following error:
Cannot insert an explicit value into a timestamp column. Use INSERT with a column list to exclude the timestamp column, or insert a DEFAULT into the timestamp column
How do I insert the current time to a timestamp column?
330
You unfortunately cannot make a change to a timestamp column, as the error implies; you are stuck with what you have. Also, each table can only have one timestamp column, so you cannot duplicate the column in any solution.
Timestamp is a synonym for rowversion. Rowversion data type is not a date or time data type. Each database has a counter that is incremented for each insert or update operation that is performed on a table that contains a rowversion column within the database. This counter is the database rowversion.
The basic syntax of “timestamp” data type in SQL is as follows : Timestamp 'date_expression time_expression'; A valid timestamp data expression consists of a date and a time, followed by an optional BC or AD.
According to MSDN, timestamp
Is a data type that exposes automatically generated, unique binary numbers within a database. timestamp is generally used as a mechanism for version-stamping table rows. The storage size is 8 bytes. The timestamp data type is just an incrementing number and does not preserve a date or a time. To record a date or time, use a datetime data type.
You're probably looking for the datetime data type instead.
53
If you have a need to copy the exact same timestamp data, change the data type in the destination table from timestamp to binary(8) -- i used varbinary(8) and it worked fine.
This obviously breaks any timestamp functionality in the destination table, so make sure you're ok with that first.
45
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
