It is not possible to convert the TimeStamp colunm into a Date & Time format. Microsoft has renamed the TimeStamp data type to RowVersion to avoid the confusion.
The timestamp/rowversion data type is really just a Database Transaction ID, and it can also be stored in a column of the Binary(8) data type.
But here is a work-around to implement the ModifiedAt, UpdatedDate column type.
This will translate (or "convert") a RowVersion column into a SmallDateTime datatype, that can be formatted according to your needs.
But you need to do a bit of work;
1. Create a table "UpdateTimeStamp" with three columns
(CreatedDate Smalldatetime, NewRowVersion, OldRowVersion) like this:
Create Table dbo.UpdateTimeStamp(
OldRowVersion binary(8) not null,
CreatedDate SmallDateTime not null constraint DF_UpdateTimeStamp_CreatedDate Default getdate(),
NewRowVersion ROWVERSION not null CONSTRAINT PKUpdateTimeStamp PRIMARY KEY CLUSTERED
Insert the first row manually
Insert into dbo.UpdateTimeStamp(OldRowVersion, CreatedDate)
VALUES (0x0000000000000000, '2000-01-01')
2. Create a SQL Agent job
Create a standard SQL Agent job which inserts one row in the table every one minute.
Make step 1 run this code:
Insert into dbo.UpdateTimeStamp(OldRowVersion)
SELECT TOP (1) NewRowVersion
ORDER BY NewRowVersion DESC
Set the schedule to run every 1 minute.
3. Query the data
You need to write your query with a join to the UpdateTimeStamp table to your table with a between join clause like this:
SELECT top 10000 mt.*, uts.CreatedDate AS ModifiedDate
FROM dim.MyTable MT
LEFT JOIN dbo.UpdateTimeStamp uts
ON MT.DT1RowVersion > OldRowVersion AND MT.DT1RowVersion <= NewRowVersion
ORDER BY uts.CreatedDate
Microsoft SQL Server is not very good at "date range join" clauses, so if you do not need minute resolution, but 10 minute accuracy is acceptable, then you can improve the query speed a bit with lowering the frequency of the SQL Agent job to 10 minute intervals.
This will only work for time periods where the SQL Agent job has been running, as it is not easy to create the dbo.UpdateTimeStamp table retrospectively.
Unless you have an old table with a RowVersion/TimeStamp datatype, and this table also never sees updates, and the table has a CreatedDate column. And the table has to be in the same database on the same server.
The RowVersion column was already added to many tables already in our Data Warehouse, because I use it as a watermark for loading data into our Data Marts.
So now I have the this feature as a debug option, when I need to investigate when things went wrong in our facts and dimensions.
Furthermore I do not need a trigger, which may have performance issues and/or stability issues, if not coded correctly.