Find Last Date Time Updated from Table

Hi All,

I was trying to get the last modified date time stamp from my table,is this any way i can pull the time stamp that modified on last run.

I do have table structure "Created Date" Modified date column in my table. and every time it gets update it update the time stamp too.

Thanks in Advance !
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pratima PharandeCommented:
By default you do not get this information from SQL Server. However everyone achieves this requirement by having a timestamp column in all tables. That is not only useful for what you are trying to do but also used as an audit mechanism to find how many or what rows arebeing changed.

The other is to have a trigger on updates/inserts that updates a Date Time
Sha1395Author Commented:
thanks pratima_mcs, i do have these columns in my table and every time its get updated or inserterd it leaves the time stamp in those colums and i wanna find the last modified or updated time stamp.
Roman GhermanSenior Software EngineerCommented:
Hi take a look at this :

"... ORDER BY TIMESTAMP DESC will give you the rows in
some order that pertains to updates - and inserts. But it can be a bit
rough. Say that you at some point reloaded the table because of some
maintenance thing, that gave you new timestamp values.
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

Bhavesh ShahLead AnalysistCommented:

check out this.
FROM TableName
WHERE TimeStamp in (Select Max(TimeStamp) FROM TableName)

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bhavesh ShahLead AnalysistCommented:

If you looking for inserted and updated time stamp separately then you need to create one more column for LastModfiedDate
i would suggest using rowversion instead of timestamp wherever possible.

From MSDN:-

The timestamp syntax is deprecated. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.
Asim NazirCommented:
Alongwith these LastUpdateddate, do add lastUpdatedBy column for audit purposes.
RowVersion converts nicely to bigint :

CREATE TABLE [dbo].[T1](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Value] [nvarchar](50) NULL,
    [RowVer] rowversion NOT NULL

insert into t1 ([value]) values ('a')
insert into t1 ([value]) values ('b')
insert into t1 ([value]) values ('c')
select Id, Value,CONVERT(bigint,rowver)as RowVer from t1
update t1 set [value] = 'x' where id = 3
select Id, Value,CONVERT(bigint,rowver)as RowVer from t1
update t1 set [value] = 'y' 
select Id, Value,CONVERT(bigint,rowver)as RowVer from t1

Id  Value   RowVer
1   a   2037
2   b   2038
3   c   2039

Id  Value   RowVer
1   a   2037
2   b   2038
3   x   2040

Id  Value   RowVer
1   y   2041
2   y   2042
3   y   2043

Open in new window

Sha1395Author Commented:
Thanks all your help, Brichsoft answered my question.

I was struggle to pull the last modified date time stamp (i have this column in my table) from my table.I think he answered very clearly.once i tested i will mark as an answer.

I personally thanks to  mayank_joshi for his code and comments
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.