144

What is the difference between Timestamp and Datetime SQL Server?

I thought Both formats are capable of storing date + time. Then, Where the difference is lying between them?

But Timestamp is not capable of storing date, time information.

Still Whats the difference?

Improve this question
4
  • 22
    Both formats are NOT capable of storing date and time. TIMESTAMP makes people think that, but really it was just a very poor naming decision by someone at Microsoft (and nobody bothered checking the SQL standard until it was too late). Commented Aug 18, 2011 at 12:54
  • 2
    @AaronBertrand a datetime column value stores values something like this 2016-06-05 04:38:56.157 in SQL server. Is it not a date and time value? Timestamp I agree as it shows a hexadecimal value like 0x00000000000007D9 which simply increments by 1 whenever I make any update in the row. Then why are you saying that both formats are not capable of storing date and time. Commented Jun 4, 2016 at 23:11
  • 4
    @RBT ok. The comment I made (5 years ago!) did not mean to suggest that a binary value could not possibly store date/time information. But rather that TIMESTAMP / ROWVERSION columns specifically, which happen to use a binary format, do not store any date or time information there. Let's not pick nits, ok? Commented Jun 5, 2016 at 3:41
  • 3
    Ohh. ok. I got an impression that you have made a generalized statement about both data types @aaronBertrand. My only concern was about datetime data type which can actually store date and time. I came across your comment today only while searching timeStamp data type. Cheers buddy! Commented Jun 5, 2016 at 3:50

2 Answers 2

Reset to default
151

According to the documentation, timestamp is a synonym for rowversion - it's automatically generated and guaranteed1 to be unique. datetime isn't - it's just a data type which handles dates and times, and can be client-specified on insert etc.

1 Assuming you use it properly, of course. See comments.

Improve this answer
Sign up to request clarification or add additional context in comments.

12 Comments

Interestingly, MSDN points out that rowversion can be cheated to be non-unique by using SELECT INTO - although they point out that this is a bad idea. If you use it properly (i.e. let the database manage the rowversion value) then it will always be unique and monotonically increasing - which can be very handy.
@JonSkeet there's seems to be a bit of a problem with DB's( which never had a timestamp/rowversion column) value. stackoverflow.com/questions/13682498/…
@HogRider: timestamp on which database system? As per this answer, it's just a row version on SQL Server, and not actually related to time. But on systems where it is based on the time, it would be on the database server's clock.
@HogRider: The database server is the computer hosting the database - typically other computers (clients) talk to the database server. It sounds like you should learn a bit more about the fundamentals of databases before getting hung up on the specifics around timestamps etc...
@MitchWheat For what it's worth, this question is more explicit, tagged better, and has 10x the views that the question you answered has. Jon Skeet answer it in 11 minutes, which is likely before it was tagged as a duplicate, but I wasn't on SO in 2011 so I can't be sure. Edit: It was tagged as a duplicate 2 years later. :|
|
34

Datetime is a datatype.

Timestamp is a method for row versioning. In fact, in sql server 2008 this column type was renamed (i.e. timestamp is deprecated) to rowversion. It basically means that every time a row is changed, this value is increased. This is done with a database counter which automatically increase for every inserted or updated row.

For more information:

http://www.sqlteam.com/article/timestamps-vs-datetime-data-types

http://msdn.microsoft.com/en-us/library/ms182776.aspx

Improve this answer

3 Comments

two different rows that where updated in the same transaction have the same row version. ??? Same row was updated teice , so the counter should be changed twice..no ?
@andreas I will up vote after you correct the statement which saying two rows can have same row version which is not correct
Interesting, I am using SQLServer 2019 and it still shows timestamp data type, not rowversion.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.