Can we convert TIMESTAMP to date time?

The short answer is, NO.

Let’s consider the following example.

USE testDB
GO

CREATE TABLE tbOrders (
iOrderID INT IDENTITY(1,1) PRIMARY KEY,
dtOrderDate DATETIME,
dcPaidAmount Decimal(9,2),
TIMESTAMP );

INSERT INTO tbOrders(dtOrderDate, dcPaidAmount) VALUES
(‘2023-11-01’, 2750),
(‘2023-11-02’, 3950),
(‘2023-11-03’, 7000);

Now retrieve the data and see the results.

SELECT * FROM tbOrders

The timestamp values assigned to the rows are –

0x000000000000A419
0x000000000000A41A
0x000000000000A41B

Now, let’s update one of the records.

UPDATE tbOrders SET dcPaidAmount = 6900 WHERE iOrderID = 3
SELECT * FROM tbOrders

If you have noticed, the timestamp value has been changed from 0x000000000000A41B to “0x000000000000A41C“.

Now, this will let you know that TIMESTAMP is not a date-time data type and it doesn’t preserve any date or time. but a hexadecimal representation of a consecutive 8-byte integer which is good to see the row-version.

Refer to the screenshots.