datetime stamping for ModifiedDate and CreatedDate

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » SQL Server Newbies » datetime stamping for ModifiedDate and...

datetime stamping for ModifiedDate and CreatedDate

Rate Topic

Display Mode

Topic Options

Author

Message

rightfield

Posted Monday, December 03, 2007 7:00 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, February 07, 2013 4:28 PM
Points: 114, Visits: 1,062

Hi experts,

I think there is a feature in MySQL that if you put two datetime columns in a table that the first one (which you would appropriately name as 'ModifiedDate') would be automatically updated if the record was updated. This meant that you didn't have to code the update of the 'ModifiedDate' and you would always know when the record was last updated. The other datetime column would be used for the created date.

When I create the table I guess I use 'default(getdate())' on the created date column but is there something that would automatically update the modified date whenever the record was modified?

Thank you.

Post #428715

Lowell

Posted Monday, December 03, 2007 7:30 AM

SSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:56 PM
Points: 11,613, Visits: 27,669

the timestamp datatype lets you know the last affected automatically, but it's not a datetime field;
here's an example:
create table #sample(somevalue varchar(30),CreatedDt datetime default getdate(),ChangedDt timestamp)

insert into #sample(somevalue) values('value 1')
insert into #sample(somevalue) values('value 2')

select * from #sample
--somevalue CreatedDt ChangedDt
--value 1 2007-12-03 09:27:27.630 0x00000000000007D1
--value 2 2007-12-03 09:27:27.630 0x00000000000007D2
update #sample set somevalue = 'Value 2' where somevalue='value 2'

select * from #sample
--somevalue CreatedDt ChangedDt
--value 1 2007-12-03 09:27:27.630 0x00000000000007D1
--Value 2 2007-12-03 09:27:27.630 0x00000000000007D3

Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #428726

rightfield

Posted Monday, December 03, 2007 9:28 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, February 07, 2013 4:28 PM
Points: 114, Visits: 1,062

Thank you Lowell. I did not know about timestamp. I have read now, however, that timestamp is not related to the date and time of an insert or change to data but only represents an increasing number. What I gather from BOL is that there is no way to later relate it to a date and time so while this is useful it won't do what I was looking for.

Thank you all the same, I know I will find timestamp useful just maybe not here.

Warm regards,

Hope

Post #428807

Lowell

Posted Monday, December 03, 2007 9:44 AM

SSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:56 PM
Points: 11,613, Visits: 27,669

yeah, in order to have a changedDt column with the last Updated/changed, you'd need to add a trigger to the table. with the trigger, you'd be able to update the column , for only the affected records, witht he current date.

I believe there's a lot of scripts and examples that would have that exact trigger example if you search for "Audit" in the Scripts section.

Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #428822

« Prev Topic | Next Topic »

Permissions