137

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?

Jack
  • 10,943
  • 13
  • 50
  • 65
MduSenthil
  • 2,019
  • 3
  • 18
  • 39
  • 19
    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). – Aaron Bertrand Aug 18 '11 at 12:54
  • 1
    @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. – RBT Jun 04 '16 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? – Aaron Bertrand Jun 05 '16 at 03: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! – RBT Jun 05 '16 at 03:50

2 Answers2

144

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.

Jon Skeet
  • 1,421,763
  • 867
  • 9,128
  • 9,194
  • 9
    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. – Joel Brown Aug 18 '11 at 11:47
  • @JonSkeet there's seems to be a bit of a problem with DB's( which never had a timestamp/rowversion column) value. http://stackoverflow.com/questions/13682498/sql-server-rowversion-definition-is-not-accurate – Royi Namir Dec 03 '12 at 11:45
  • @JonSkeet i want to clarify something about `timestap` is the clock of the computer you are using?if for instance i change the clock (time and data) will the timestamp be affected?I want to ask this because i want to use it as reminder that a certain time has elapsed.i have a question here http://stackoverflow.com/questions/30302234/jquery-or-php-time-reminder?noredirect=1#comment48700626_30302234 – Brownman Revival May 19 '15 at 02:38
  • 1
    @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. – Jon Skeet May 19 '15 at 05:42
  • @JonSkeet what do you mean by database system?im sorry im new to server side programming. i am using mysql sa database and now i am using xampp as my local host. – Brownman Revival May 19 '15 at 06:32
  • 1
    @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... – Jon Skeet May 19 '15 at 07:07
  • @JonSkeet i know that one sorry i thought you were saying other things. is the server time cannot be changed?that will be the thing i want to clarify. Timestamp will get server time not computer/user time?i am not clear with this one i thought its the user computer's time that is being used as time stamp – Brownman Revival May 19 '15 at 07:14
  • @HogRider: It sounds like you should really be asking a new question with far more details - `Timestamp` is mostly just a data type, which can be initialized with whatever value you like, in most databases. In particular, you've added comments to an answer to a question which is specific to SQL Server, and therefore not really relevant to you... – Jon Skeet May 19 '15 at 07:49
  • @JonSkeet i have a question already but some one put -1 on it this is the link http://stackoverflow.com/questions/30302234/jquery-or-php-time-reminder – Brownman Revival May 19 '15 at 08:08
  • @HogRider: That doesn't even *mention* MySql. The question seems really vague to me - talking about "the computer" for example, when clearly in most cases there are multiple computers (end user, web server, database server for example). I'm not surprised it's been downvoted. Please read http://tinyurl.com/stack-hints and rewrite it. – Jon Skeet May 19 '15 at 08:10
  • Got to love the fact that Jon Skeet's duplicate answer to a question gets twice the upvotes (the original answer is mine...). SO, I think you might be broken. – Mitch Wheat Aug 30 '18 at 01:55
  • 2
    @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. :| – dckuehn Oct 23 '18 at 21:01
  • @MitchWheat I think Jon has a fan club. – Ama Jul 16 '21 at 16:52
30

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

Andreas Ågren
  • 3,879
  • 24
  • 33
  • 1
    _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 ? – Royi Namir Dec 03 '12 at 11:27
  • @andreas I will up vote after you correct the statement which saying two rows can have same row version which is not correct – dejjub-AIS Oct 26 '17 at 07:06