SQL Row inserted Date

Question

5.00/5 (1 vote)

See more:

I've a SQL table of Data which doesn't have any specific column for Row updated date or time. Now i want know when the row was inserted.!!

Thanx in Advance.

Posted 21-Oct-13 0:59am

Sandeep Mogaveer

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Ehsan Skardu · Answer 1 · 2013-10-21T01:13:00

Try to use this function,

SQL

SELECT *,fn_TablesLastUpdateDate(0) as InsertedDate from yourtable

SQL

CREATE FUNCTIOn fn_TablesLastUpdateDate(@Date NVARCHAR(20))

RETURNS @table TABLE(TableName NVARCHAR(40), LastUpdated Datetime)

AS

BEGIN


IF(@Date='') OR (@Date Is Null) OR (@Date='0')

    BEGIN
        INSERT INTO @table
        SELECT TOP 100 PERCENT TABLENAME,LASTUPDATED FROM 
        (
            SELECT  B.NAME AS 'TABLENAME', MAX(STATS_DATE (ID,INDID)) AS LASTUPDATED
            FROM    SYS.SYSINDEXES AS A
                    INNER JOIN SYS.OBJECTS AS B ON A.ID = B.OBJECT_ID
            WHERE   B.TYPE = 'U'  AND STATS_DATE (ID,INDID) IS NOT NULL 
            GROUP BY B.NAME
        ) AS A
        ORDER BY LASTUPDATED DESC
    END
ELSE

    BEGIN
        INSERT INTO @table
        SELECT TOP 100 PERCENT TABLENAME,LASTUPDATED FROM 
        (
            SELECT  B.NAME AS 'TABLENAME', MAX(STATS_DATE (ID,INDID)) AS LASTUPDATED,
                    CONVERT(VARCHAR, MAX(STATS_DATE (ID,INDID)), 103) as Date
            FROM    SYS.SYSINDEXES AS A
                    INNER JOIN SYS.OBJECTS AS B ON A.ID = B.OBJECT_ID
            WHERE   B.TYPE = 'U'  AND STATS_DATE (ID,INDID) IS NOT NULL 
            GROUP BY B.NAME
        ) AS A
        WHERE Date=@Date
        ORDER BY LASTUPDATED DESC
    END
RETURN

END

*Karthik Harve* · Answer 2 · 2013-10-21T01:26:00

Solution 2

hi,
if you want to check updated date and time of the sql row, use the query as below:

SQL

SELECT top 10 col.name 'ColumnName', MAX(STATS_DATE (ID,INDID)) 'DateTime'
    FROM    SYS.SYSINDEXES as Ind
INNER JOIN SYS.columns as Col ON Ind.ID = Col.OBJECT_ID
WHERE STATS_DATE (ID,INDID) IS NOT NULL and Col.name like '%ColummnName%'
GROUP BY Col.name

hope it helps.

Posted 21-Oct-13 1:26am

Karthik Harve

Comments

Sandeep Mogaveer 21-Oct-13 7:31am

Thank you. But Looking for create Date and Time!!

Tom Marvolo Riddle 21-Oct-13 7:38am

Then use getdate().

Sandeep Mogaveer 21-Oct-13 8:53am

Row insert Datetime , not current date and time.