SQL Server: Cannot insert an explicit value into a timestamp column

sql sql-server-2008 insert timestamp
juergen d picture juergen d · Apr 21, 2012 · Viewed 159.6k times · Source

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?

Answer

Andomar picture Andomar · Apr 21, 2012

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.

Related questions

Perform insert for each row taken from a select?

I have a number of records that I need to insert into multiple tables. Every other column will be a constant. Poor pseudo code below - this is what I want to do: create table #temp_buildings ( building_id varchar(20) ) …

Read More
Inserting multiple rows into a SQL Server table using a table variable

I am currently using SQL Server 2008, and I am trying to create a statement using a table variable to insert multiple rows into the table. As it stands right now, I have to insert the information being added in 4 different …

Read More
How can I get column names from a table in SQL Server?

I want to query the name of all columns of a table. I found how to do this in: Oracle MySQL PostgreSQL But I also need to know: how can this be done in Microsoft SQL Server (2008 in my case)?

Read More