Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2007
    Posts
    25

    Red face Unanswered: Update MONTH part of a datetime column

    I have a datetime column where I need to change the month part only of the date.

    from 2009-03-05 13:46:28.00
    to 2009-04-05 13:46:28.00

    There are over 10,000 of these records that I have to change, the good part is that the primary key of the records are in sequence so I can use BETWEEN in the where statement

    I have tried several diff and they all give me syntax error.

    For example I can use the following in my test database;

    select trans_id, month (local_dtime) local, month (proc_dtime) proc from trans
    where trans_id between
    596895 and 596897;

    And i get:
    trans_id local proc

    596895 3 3
    596896 3 3
    596897 3 3

    If I use:
    update trans set month (proc_dtime) = 4
    where trans_id between 596895 and 596897;

    or
    update trans set month (proc_dtime) = '04'
    where trans_id between 596895 and 596897;

    I get -201 syntax error

  2. #2
    Join Date
    Apr 2007
    Location
    Jundiai / SP - Brasil
    Posts
    311
    This way of update is invalid.
    So, have a several ways to solve this, but this can depend of the data type if is date or datetime .

    If you is working with DATE type, this update maybe help:
    Code:
    update trans set proc_dtime= mdy(4,day(proc_dtime),year(proc_dtime))
    where trans_id between 596895 and 596897;
    If use DATETIME I need to test some codes here... tell me if is the case.
    ________________________________________
    César Inacio Martins
    Jundiai / SP - Brasil
    http://www.imartins.com.br/informix - em Português
    http://www.imartins.com.br/informix - English (translated by Google).
    ________________________________________

  3. #3
    Join Date
    Jul 2008
    Posts
    8
    Use update tablename set columnname = columnname + 1 units month.
    Doesn't work for value in 31/05, you have to decide how to convert them.

  4. #4
    Join Date
    Jun 2010
    Posts
    2

    Red face update datetime field

    I need a lill help from you all smartys!

    I am working on an update of a datetime field and receiving the following error.

    Query:

    Update tbl
    SET Month(column 1) = Month(Column 2),
    Day(column 1) = Day(column 2)
    WHERE Month(column 1) = Day(column 2)
    and Day(column 1) = Month(column 2)
    and id in (111,222,333)

    Error:

    Msg 102, Level 15, State 1, Line 2
    Incorrect syntax near ‘)’.

    Please assist.

    Thanks, — imransi —

  5. #5
    Join Date
    Jun 2009
    Location
    Lisboa, Portugal
    Posts
    78
    Hi,

    If you have the same hour, minute, second and fraction on a datetime column, why don´t you do like this:

    update trans set trans_id = "2009-04-05 13:46:28.00" where trans_id = "2009-03-05 13:46:28.00";

    If you dont have the same hour, minute, second and fraction then you should use a EXEC SQL or try to write a procedure.
    LS

  6. #6
    Join Date
    Jun 2010
    Posts
    2

    Red face update datetime field

    First of all Thank you for replying.

    You are right, its NOT the same hour, minute and second. I have to update the column1 day to match the month of column2 and update the column1 month to match the day of column2.

    Example:
    Column1 Column2
    2009-01-08 19:42:00.000 2009-08-01 19:42:00.000

    Also, I don't know the code to update just the month and also just the day with in a datetime column.
    Please assist !

  7. #7
    Join Date
    Aug 2010
    Posts
    1
    Thanks for the information guys!! I have been experiencing the same problem. I will try this codes.

  8. #8
    Join Date
    Aug 2010
    Posts
    1

    Update

    hello!!!

    Use update tablename set columnname = columnname + 1 units month.
    Doesn't work for value in 31/05, you have to decide how to convert them.

Posting Permissions

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