I need a way to update the month value on a dateTime field in my db. I'm being past an int value for the month and need to use that for the update. Is it possible to do this in the sql statement or would I be best doing it in c# in the webservice?
+1
A:
Shift down and then up again:
UPDATE table
SET datecol = DATEADD(m, @newmonth, DATEADD(m, -MONTH(datecol), datecol))
WHERE id = @id
or, more simply:
UPDATE table
SET datecol = DATEADD(m, @newmonth - MONTH(datecol), datecol)
WHERE id = @id
Cade Roux
2008-10-02 14:27:10
Thanks, I get this error message though.Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.The statement has been terminated.
2008-10-02 14:34:32
You need to post your code, my query does not have anything to do with that, because it does contain any subqueries.
Cade Roux
2008-10-02 14:38:57
This is what I have.DECLARE @newMonth intSET @newMonth = 5;UPDATE dbo.tb_bookingsSET startDateTime = DATEADD(month, @newMonth - DATEPART(month, startDateTime) , startDateTime)WHERE requestPackID = '383';
2008-10-03 00:15:38
I think you might have a trigger problem. That code doesn't have any subqueries, and even if multiple rows are to be affected it will work. Now, if multiple rows are affected the trigger may not expect this - remember an UPDATE trigger will fire only once on this statement.
Cade Roux
2008-10-03 02:55:08
Thanks, I think you may be right with the trigger. I'll look into it.
2008-10-03 05:25:04
+1
A:
You can do this all in TSQL in Sql Server. Check out the DateDiff and DateAdd functions.
I expect this would work:
DECLARE @newMonth int
SET @newMonth = 5 --As an example
UPDATE dbo.TheTable
SET DateField = DATEADD(month, @newMonth - DATEPART(month, DateField) , DateField)
akmad
2008-10-02 14:39:00
Thanks however I need to update multiple rows and am having errors regarding the subquery returning more than one value.
2008-10-02 14:54:10
There aren't any subqueries in this example and this will work fine when updating multiple rows in a table.You may want to post the specific code you are having problems with.
akmad
2008-10-02 15:06:29