Created Date of a Record in a Table

Question

Created Date of a Record in a Table

Prasanna Devarajan

SSC-Addicted

Points: 402
More actions
January 22, 2010 at 3:28 am

#209358

Hi all,
My table does not have a created date or modified date column.
My query is to get the created date of all the records in a particular table .Is it possible.
I have tried various ways and found its not possible,still wanted to get it confirmed.
If there s any ways kindly help me.
Thank you all in advance.

Viewing 9 posts - 1 through 8 (of 8 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

dp-01 (1/22/2010)
My table does not have a created date or modified date column.
My query is to get the created date of all the records in a particular table .Is it possible.

No.

If you need to know created date of rows, you need a column of type datetime that has a default of getdate(). If you do not have that, there is no way that you can tell when the rows were created.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Prasanna Devarajan SSC-Addicted Points: 402 More actions · Answer 2

yes.

Thank you for your quick reply on that Shaw.

Good Day .

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 3

Try

CREATE TABLE MYTABLE (Name Varchar(60),IPDate DATETIME DEFAULT GETDATE())

INSERT INTO MYTABLE (Name) VALUES('MYName' )

Select * from MYTABLE

you will be getting the date and time when a new record is inserted to ur table

Thanks
Parthi

Prasanna Devarajan SSC-Addicted Points: 402 More actions · Answer 4

Parthi , I hope you have misunderstood my query.

I have already my schema and it is "Live".

No space to create a new table with the column.

Anyways thanks for your reply.

Good day.

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 5

I have already my schema and it is "Live".
No space to create a new table with the column.

What is mean by No space to create a new table with the column.? what are you trying to say

ALTER TABLE MYTABLE ADD date DATETIME

go

ALTER TABLE dbo.MyTABLE ADD CONSTRAINT

DF_MyTABLE_date DEFAULT GETDATE() FOR date

Thanks
Parthi

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

parthi-1705 (1/22/2010)
What is mean by No space to create a new table with the column.? what are you trying to say
ALTER TABLE MYTABLE ADD date DATETIME
go
ALTER TABLE dbo.MyTABLE ADD CONSTRAINT
DF_MyTABLE_date DEFAULT GETDATE() FOR date

Which will help future rows, but is not going to tell when rows that are already in the table were created. That, as far as I can tell, is what Prasanna wants, to find out when rows that are already in the table were created.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Prasanna Devarajan SSC-Addicted Points: 402 More actions · Answer 7

hmmm ... I have already lacks and lacks of data in my table which is already inserted.

you are trying to give a solution for future but presently I need to get the records Inserted date in that table. Got it Parthi.

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 8

presently I need to get the records Inserted date in that table.

Then use 3rd party Tool log Rescue (if you u have from beginning) and find date and time row by row record inserted in your table you will be getting.If you or not having Log then just leave it no other way is there to update the previous data's

If You need older datas date and time then do it one by one as it in Log file.

Thanks
Parthi