CURRENT DATE and TIME Question

Discussion in 'ASP General' started by TenOCC, Aug 3, 2005.

  1. TenOCC

    TenOCC Guest

    I have an event that update my database, I would like to record the current
    date and time when the record was updated.

    Not quite sure of the command

    Conn.Execute ("UPDATE dM SET OpenDate = ???????)
     
    TenOCC, Aug 3, 2005
    #1
    1. Advertising

  2. CURRENT_TIMESTAMP





    "TenOCC" <> wrote in message
    news:...
    >I have an event that update my database, I would like to record the current
    > date and time when the record was updated.
    >
    > Not quite sure of the command
    >
    > Conn.Execute ("UPDATE dM SET OpenDate = ???????)
    >
    >
     
    Aaron Bertrand [SQL Server MVP], Aug 3, 2005
    #2
    1. Advertising

  3. Oh, sorry, you forgot to mention which database you were using. Please
    specify.

    SQL Server:

    conn.execute "UPDATE dM SET OpenDate = CURRENT_TIMESTAMP WHERE ...",,129

    Access:

    conn.execute "UPDATE dM SET OpenDate = Now() WHERE ...",,129






    "TenOCC" <> wrote in message
    news:...
    >I have an event that update my database, I would like to record the current
    > date and time when the record was updated.
    >
    > Not quite sure of the command
    >
    > Conn.Execute ("UPDATE dM SET OpenDate = ???????)
    >
    >
     
    Aaron Bertrand [SQL Server MVP], Aug 3, 2005
    #3
  4. TenOCC wrote:
    > I have an event that update my database, I would like to record the
    > current date and time when the record was updated.
    >
    > Not quite sure of the command
    >
    > Conn.Execute ("UPDATE dM SET OpenDate = ???????)


    What database? Type and version please.

    Bob Barrows
    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
     
    Bob Barrows [MVP], Aug 3, 2005
    #4
  5. TenOCC

    TenOCC Guest

    Sorry SQL Server 2000

    "Bob Barrows [MVP]" <> wrote in message
    news:...
    > TenOCC wrote:
    > > I have an event that update my database, I would like to record the
    > > current date and time when the record was updated.
    > >
    > > Not quite sure of the command
    > >
    > > Conn.Execute ("UPDATE dM SET OpenDate = ???????)

    >
    > What database? Type and version please.
    >
    > Bob Barrows
    > --
    > Microsoft MVP -- ASP/ASP.NET
    > Please reply to the newsgroup. The email account listed in my From
    > header is my spam trap, so I don't check it very often. You will get a
    > quicker response by posting to the newsgroup.
    >
    >
     
    TenOCC, Aug 3, 2005
    #5
  6. Then you can use an update trigger in your database to update this column (I
    am going to assume you have a column called id which is the primary key for
    the dm table):

    CREATE TRIGGER tr_dm_OpenDate
    ON dm
    FOR UPDATE, INSERT AS
    UPDATE dm
    SET OpenDate = CURRENT_TIMESTAMP
    WHERE id IN
    (SELECT id FROM inserted)

    It's better to use a trigger for this because your table may be updated by
    sources other than your asp application.

    Bob Barrows

    TenOCC wrote:
    > Sorry SQL Server 2000
    >
    > "Bob Barrows [MVP]" <> wrote in message
    > news:...
    >> TenOCC wrote:
    >>> I have an event that update my database, I would like to record the
    >>> current date and time when the record was updated.
    >>>
    >>> Not quite sure of the command
    >>>
    >>> Conn.Execute ("UPDATE dM SET OpenDate = ???????)

    >>
    >> What database? Type and version please.
    >>

    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
     
    Bob Barrows [MVP], Aug 3, 2005
    #6
  7. TenOCC

    SP Guest

    Or you can just use

    "Update dM Set OpenDate = getdate() where idfield = XXX...."


    "TenOCC" <> wrote in message
    news:...
    > Sorry SQL Server 2000
    >
    > "Bob Barrows [MVP]" <> wrote in message
    > news:...
    >> TenOCC wrote:
    >> > I have an event that update my database, I would like to record the
    >> > current date and time when the record was updated.
    >> >
    >> > Not quite sure of the command
    >> >
    >> > Conn.Execute ("UPDATE dM SET OpenDate = ???????)

    >>
    >> What database? Type and version please.
    >>
    >> Bob Barrows
    >> --
    >> Microsoft MVP -- ASP/ASP.NET
    >> Please reply to the newsgroup. The email account listed in my From
    >> header is my spam trap, so I don't check it very often. You will get a
    >> quicker response by posting to the newsgroup.
    >>
    >>

    >
    >
     
    SP, Aug 3, 2005
    #7
  8. > Or you can just use
    >
    > "Update dM Set OpenDate = getdate() where idfield = XXX...."


    You probably missed Bob's point, which was that it is not necessarily the
    case that updates can be restricted to the ASP code alone, never mind how
    many places the code will have to change (yes, stored procedures should be
    used, but that's a different argument, and we are not clear on whether they
    are currently in place).

    There are arguments for both sides. Triggers definitely help to prevent ad
    hoc updates from other sources (query analyzer, rogue applications etc) from
    being missed and the row(s) not updated, however there is definitely a hit
    on performance -- especially if some ******* says "UPDATE table SET col =
    col" without a where clause.
     
    Aaron Bertrand [SQL Server MVP], Aug 3, 2005
    #8
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Manu
    Replies:
    1
    Views:
    6,193
    Natty Gur
    Jul 20, 2003
  2. James P.
    Replies:
    7
    Views:
    12,751
    phamtasmic
    Jul 13, 2004
  3. Chris Berg
    Replies:
    0
    Views:
    848
    Chris Berg
    Oct 27, 2003
  4. Peter Grison

    Date, date date date....

    Peter Grison, May 28, 2004, in forum: Java
    Replies:
    10
    Views:
    3,374
    Michael Borgwardt
    May 30, 2004
  5. Keith Cochrane
    Replies:
    2
    Views:
    651
    Keith Cochrane
    Aug 6, 2006
Loading...

Share This Page