Results 1 to 4 of 4

Thread: TimeStamp

  1. #1
    Join Date
    Feb 2008
    Posts
    2

    Unanswered: TimeStamp

    I am working with a table which saves the last_mod_date which the row in the database was updated. I am going to change this value for testing purposes, however i need to be able to enter back in the original date and time when i am finished testing. I have tried the statement below but it throws up an error "Arithmetic overflow during explicit conversion of VARCHAR value '18/01/2008 08:42:29.302' to a DATETIME field". Can anyone assist me in where i am going wrong.

    Code:
    update clm_t 
    set last_mod_user = 'user', last_mod_time = convert(char(23), '18/01/2008 08:42:27.302',109)
    where id_inv = 123456
    Thanks in advance

  2. #2
    Join Date
    Dec 2007
    Location
    Chennai,India
    Posts
    12
    convert(char(23), '2008/01/18 08:42:27.302',109) should work.

    http://infocenter.sybase.com/help/in...man/X41864.htm
    Last edited by O'sambo; 02-06-08 at 02:44.

  3. #3
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    converting a char to a char 109 format has no effect
    then the update tries to convert that to a datetime expecting mm/dd/yy
    and overflow on month 18

    You should convert to datetime
    e.g.
    Code:
    select 
     convert(datetime, '18/1/2008 08:42:27.302',103)
    ,convert(datetime, '1/18/2008 08:42:27.302',101)
    or
    Code:
    set dateformat dmy
    select convert(datetime,'18/1/2008 08:42:27.302')
    Last edited by pdreyer; 02-06-08 at 06:44.

  4. #4
    Join Date
    Feb 2008
    Posts
    2
    Sorry about delay in replying.

    Thank you very much for your help. This was exactly what i needed to get the data insert

Posting Permissions

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