question

Usually, an erroneous desire to convert the column from **timestamp** to **datetime** comes from the former's name, but the reality is that the timestamp data type has absolutely nothing to do with date and time, much like the evil float SQL Server data type has nothing to do with the friendly float of the front-end languages, such as C# yet so many people use the float data type in database tables just because the name is familiar without realizing that they should almost never do it. Timestamps exist to track the relative time within the database scope, and this relative time has nothing to do with a clock. Attempting to convert the timestamp value to datetime is guaranteed to convert it to the wrong date. There are only 2 things timestamp and datetime have in common: - Both need 8 bytes of storage - Both are ever-increasing as far as the system clock is concerned Semantically, however, they are very different. The datetime storage consists of a pair of 4 byte integers, left 4 bytes to store the number of days from the zero date of '19000101' at midnight and the right 4 bytes to store the number of ticks from midnight today. Timestamp though is best thought of as a binary(8).

Actully the Issue is that we have to import the data from Prod to dev database but we are getting error "Can not insert data in table XYZ due to read only columm "on this column have timestamp datatype.

If you're using a script, re-write your script to avoid trying to insert into that column. You might be doing something like insert into [sometable] select * from [someprodtable] Name the columns in your query: insert into [sometable](col1, col2, col3) select col1, col2, col3 from [someprodtable] If you are using a tool to import the data, there should be some option to avoid specific columns.

@basit Usually, it takes to disregard the values in this column. They are typically not used by any queries or procedures, so when you import your data from prod to dev, do not mention this column and the values will be populated automatically. Sure they will be different from those in production, but it should not be a big problem. The only time when the timestamp values are actually valuable is when there is a need to investigate some open issue, such as which record into which table was inserted first. Using the datetime values for such investigations is typically useless because it is possible for a couple of transactions to complete within the same tick of the system clock, but using the timestamp values instead will always reveal the actual order in which the data was inserted. Timestamp is scoped to the database, so it does help in those scenarios, but not really for anything else. What I mean is that if you insert a record into table with timestamp column, the value of current database stamp changes and remains even if you rollback transaction. To get the value of the current stamp you can query @@dbts:

select @@dbts current_stamp;

@Kevin Feasel My comment is a product of the wonders of async operations. In other words, I did not try to restate your comment, it just so happened that I started typing just before you saved yours and although I used both fingers, it took about 5 minutes to finish the comment :)

Two reasons for -1: 1. Timestamp in SQL Server Unix Timestamp 2. Spam.