timestamp and datetime

Question

i have a table with two columns first column is a (currentdate)timestamp and the other one is a (dateReturn)datetime column.i i need to get the difference between currentdate and dateReturn from number of days.is this possible?

Answer 1

No, this is not possible.

Contrary to popular belief (and the name itself), a timestamp column is not actually related to time.

Disclaimer: This answer is specific to Sql Server (as per the tags of the question), other database engines might have different implementations here.*

Instead it is a 64-bit number that steadily increases. The value of the number is shared between all tables in a single database that has such a timestamp column. In other words, if you have two such tables, modify a row in one, and modify a row in the other, the value of the timestamp in the first table would be X, and in the second table would be X+1.

as per the documentation of timestamp:

Each database has a counter that is incremented for each insert or update operation that is performed on a table that contains a timestamp column within the database. This counter is the database timestamp. This tracks a relative time within a database, not an actual time that can be associated with a clock.

As such, the value is not related to the actual date and/or time the row was inserted or last modified, and can not be converted to such a date/time in any way.

The only purpose of a timestamp column is to have something unique, that is guaranteed to be increasing. Since the value is 64 bit, you will have access to 18 446 744 073 709 551 616 operations which require a timestamp column before the value resets. If you have a database that can handle a million such operations every second, you will still have to wait around 584 554 years before the value resets, so I would say it meets the uniqueness requirements.

Answer 2

The timestamp data type in sql server does not store a date or time. It is more of a version maintaining column which is updated every time the row is updated. So you should reconsider the data type of this column