While this seems like a simple thing to do, I recently discovered so many ways not to do it (also known as failing) that I just had to document this one.
While this seems like a simple thing to do, I recently discovered so many ways not to do it (also known as failing) that I just had to document this one.
It is very common to want a column of your SQL table to be a timestamp of when the row was written. At times like these, it's nice to know that there is a "Timestamp" datatype… that does not help at all. So, how do you do it?
Solution: "datetime" Datatype
The data type must be "datetime" and the Default Value or Binding must be "getdate()". When I filled in the Default Value or Binding, I used "CURRENT_TIMESTAMP", which was automatically updated to "getdate()", as seen below.
Timestamp Datatype
So what about the "timestamp" datatype? Microsoft states:
The Transact-SQL timestamp data type is not the same as the timestamp data type defined in the SQL-92 standard. The SQL-92 timestamp data type is equivalent to the Transact-SQL datetime data type.
In other words, in SQL Server, if you want a timestamp, don't use "timestamp", use "datetime". If you use the "timestamp" datatype, you'll see data like this in your table:
