SQL Server 2000 Date and Time Recording Function

Question

I was given the task of displaying when a record was added to the database, however, previous developers have never dealt with this area, and I can not go back and make dates for all existing records. There is an easy way to extract the record creation date from a SQL server 2000 query.

 SELECT RECORD_CREATED_DATE FROM tblSomething WHERE idField = 1 

RECORD_CREATED_DATE not a field in an existing table. Is there any SQL function to get this information?

Answer 1

If it is not stored as a field, information is lost after reusing the transaction log (usually daily), and possibly even earlier.

Answer 2

No, unfortunately the insertion date or the last update is not saved automatically with each entry.

To do this, you need to create two datetime columns in your table (for example, CreatedOn , UpdatedOn ), and then set CreatedOn = getdate () in INSERT , and set UpdatedOn = getdate () in the UPDATE trigger .. p>

 CREATE TRIGGER tgr_tblMain_Insert ON dbo.tblMain AFTER INSERT AS BEGIN set nocount on update dbo.tblMain set CreatedOn = getdate(), CreatedBy = session_user where tblMain.ID = INSERTED.ID END 

I also like the CreatedBy and UpdateBy varchar (20) columns that I set to session_user or update using other methods. p>

Answer 3

I do not know how you can get this information for existing records. However, in the future you can create an audit table that stores the table name and RecordCreateDate (or something similar).

Then, for the corresponding tables, you can create an insert trigger:

 CREATE TRIGGER trigger_RecordInsertDate ON YourTableName AFTER INSERT AS BEGIN -- T-SQL code for inserting a record and timestamp -- to the audit table goes here END 

Answer 4

To begin with, I will now use this information. Create two columns: InsertedDate, LastUpdatedDate. Use the default getdate () for the first and the update trigger to populate the second (you might also want to consider UpdateBy). Then I will write a query to display the information using the CASE statement to display the date, if any, and to display "Unknown" this field is null. This becomes more complicated if you need to keep a record of all updates. Then you need to use audit tables.

Answer 5

create another column and give it the default value getdate (), which will take care of the inserted date, for the updated date you will need to write an update trigger