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

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:
If use DATETIME I need to test some codes here... tell me if is the case.

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.

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 —

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.

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 !

Thanks for the information guys!! I have been experiencing the same problem. I will try this codes.

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.