Display the latest updated data

Question

I have to display the data that currently being update. The problem is, I'm using the CONVERT function for the columns that i need to display. This is my code for fetch the data with status 'New'. This code is before updating the status. SELECT CONVERT ( varchar (10), Start_RG, 2) AS Start_RG, CONVERT ( varchar (10), End_RG, 2) AS End_RG FROM RG WHERE Status = 'New' This is the code for updating the status to 'In Use' UPDATE TOP (1) RG SET Status = 'In Use' OUTPUT deleted.Start_RG, deleted.End_RG, deleted.Status WHERE Status = 'New' How can i fetch the Start_RG and End_RG that currently updated the status to In Use

Kiran Mohanty · Answer

Then you will have to define a trigger and this trigger should save last status change in a new table. All you have to do is, select from new table and inner join with current table. Below link may help you. https://database.guide/create-a-last-modified-column-in-sql-server/#:~:text=Some%20database%20tables%20include%20a,trigger%20to%20perform%20this%20update.

Amit Gupta · Answer

You should consider by adding 'updatedDate' column ( which will be quite handy and useful in near future) and then you can easily use order by UpdateDate desc

Mageshwaran R · Answer

Hi AK, please refer this link: https://stackoverflow.com/questions/31634918/how-do-i-add-a-last-modified-and-created-column-in-a-sql-server-table

As Ak · Answer

@Kiran Mohanty No, I dont have LastUpdatedDate column.

Kiran Mohanty · Answer

Do you have LastUpdatedDate column ? If yes then you can do order by on LastUpdatedDate.