Professional Web Applications Themes

Question about storing "date modified" - Microsoft SQL / MS SQL Server

Hi, In our application, we want to store the data a record was modified. Would it be appropriate to use the "Timestamp" datatype? If not, which would be appropriate. For example, if a user updates his/her profile, we wanted to store the data modified.. WestSide... .. *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it!...

  1. #1

    Default Question about storing "date modified"

    Hi,

    In our application, we want to store the data a record was modified.

    Would it be appropriate to use the "Timestamp" datatype?

    If not, which would be appropriate.

    For example, if a user updates his/her profile, we wanted to store the
    data modified..

    WestSide...

    ..

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Westside Guest

  2. #2

    Default Re: Question about storing "date modified"

    > In our application, we want to store the data a record was modified. 

    Unfortunately timestamp data type in SQL Server has nothing to do with actal
    data and time, the name is misleading.
     

    Create your own datetime column and add a update trigger to the table -
    modify the datetime column from the trigger.

    --
    Dejan Sarka, SQL Server MVP
    FAQ from Neil & others at: http://www.sqlserverfaq.com
    Please reply only to the newsgroups.
    PASS - the definitive, global community
    for SQL Server professionals - http://www.sqlpass.org


    Dejan Guest

  3. #3

    Default Re: Question about storing "date modified"

    Create a column default like this:

    CREATE TABLE Sometable (col1 INTEGER PRIMARY KEY, col2 VARCHAR(10) NOT NULL,
    moddatetime DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP)

    INSERT INTO Sometable (col1, col2) VALUES (1,'ABC')

    To capture the modification datetime, either specify DEFAULT in your UPDATE
    statement

    UPDATE Sometable
    SET col2 = 'DEF',
    moddatetime = DEFAULT

    or use an update trigger.

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --



    David Guest

Similar Threads

  1. #40717 [NEW]: date "01-01-1968" inaccessible via date function
    By manuel dot pinhao at nvisible dot pt in forum PHP Bugs
    Replies: 13
    Last Post: April 23rd, 02:47 PM
  2. #39106 [Opn->Bgs]: date("w") incompatible with date("W")
    By tony2001@php.net in forum PHP Bugs
    Replies: 0
    Last Post: October 10th, 10:53 AM
  3. #39106 [NEW]: date("w") incompatible with date("W")
    By felixmallek at gmx dot de in forum PHP Bugs
    Replies: 0
    Last Post: October 10th, 10:42 AM
  4. Keep getting message: "File as been modified outside Illustrator" on every save
    By Rydog23@adobeforums.com in forum Adobe Illustrator Macintosh
    Replies: 4
    Last Post: July 7th, 03:40 PM
  5. Question about "Public Sub" vs "Private Sub" vs "Sub"
    By michaaal in forum ASP Database
    Replies: 1
    Last Post: October 18th, 07:15 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139