Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2005
    Location
    SW England
    Posts
    102

    Unanswered: I didn't record the submit date

    Anyone know how to query my table for entries between specific times if I haven't explicitly recorded the time of entry?
    I gather the DB always records it but I don't know how to use that info.
    Thanks

    PS I actually just need to know how many records were entered between specific times.
    Last edited by darkmunk; 10-25-10 at 08:31. Reason: further parameters

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by darkmunk View Post
    I gather the DB always records it ...
    sorry it does not
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2005
    Location
    SW England
    Posts
    102
    Hey Rudy, long time...
    Your Beans DB is still going strong
    So is there anything I can do? or has that date info never been recorded?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i'm afraid you can only add the datetime column to the table now, and fill in the missing values for past history as best you can
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The SQL Server database engine does not automagically record when rows were added or changed.

    If you are looking for the rows that were inserted at a given time, you can add a DATETIME column with a DEFAULT value of GetDate() which will record when the row was inserted into the table.

    If you need to record when the row was last changed, then you'll need to add a DATETIME column and a trigger to keep the column updated.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Pat Phelan View Post
    ... and a trigger to keep the column updated.
    i understand what you're saying but i wouldn't do it that way

    you might want to do an update that doesn't trigger the trigger, so to speak, e.g. mass updates...
    UPDATE personnel SET title='MS' WHERE title IN ('Miss','Mrs')
    you could temporarily disable the trigger, sure, but that would require a DBA

    my preference is to have the SQL do it...
    Code:
    UPDATE ...
       SET ...
       , lastupdated = CurDate()
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I certainly wouldn't do anything to my trigger just because the user needs to update multiple rows. The trigger still needs to update the column to show the date and time of modification for those rows which were changed.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •