SQL Server 2000 Function for record created datetime

Question

I was given a task to display when a record in the database was added, however, the previous developers never made a field for this, and I can't go back and make up dates for all the existing records. Is there an easy way to extract out a record Creation date from a SQL server 2000 query.

SELECT RECORD_CREATED_DATE FROM tblSomething WHERE idField = 1

The RECORD_CREATED_DATE isn't a field in the existing table. Is there some sort of SQL Function to get this information?

Answer 1

If it's not stored as a field, the info is lost after the transaction log recycles (typically daily), and maybe even sooner.

Answer 2

I'm not aware of a way you can get this information for existing records. However, going forward you can create an audit table that stores the TableName and RecordCreateDate (or something similar.

Then, for the relevant tables you can make 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 3

No, unfortunately date of insert or last update are not automatically stored with each record.

To do that, you need to create two datetime columns in your table (e.g. CreatedOn, UpdatedOn) and then in an INSERT trigger set the CreatedOn = getdate() and in the UPDATE trigger set the UpdatedOn = getdate().

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 to create CreatedBy and UpdatedBy varchar(20) columns which I set to session_user or update through other methods.

Answer 4

create another column and give it a default of getdate() that will take care of insetred date, for updated date you will need to write an update trigger

Answer 5

I would start with putting this information in from now on. Create two columns, InsertedDate, LastUpdatedDate. Use a default value of getdate() on the first and an update trigger to populate the second (might want to consider UpdatedBy as well). Then I would write a query to display the information using the CASE Statement to display the date if there is one and to display "Unknown" is the field is null. This gets more complicated if you need to store a record of all the updates. Then you need to use audit tables.