datetime stamping for ModifiedDate and CreatedDate

Question

Post reply

datetime stamping for ModifiedDate and CreatedDate

rightfield

SSCrazy

Points: 2418
More actions
December 3, 2007 at 7:00 am

#179739

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.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323482 More actions · Answer 1

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

rightfield SSCrazy Points: 2418 More actions · Answer 2

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

Lowell SSC Guru Points: 323482 More actions · Answer 3

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!