April 30, 2010 at 11:19 am
Hello,
I have a table that contains a month int field and a year in field and I need to be able to shift the "date" forward and backward. I am trying to do an update with a subselect that converts the month and year into a date, then adds using the dateadd function, and then extracts the new month and year to update the field.
I think I am close, but I realize the sub select won't draw the same record as the update is working on, but I seem to be unable to resolve this issue.
I would be very grateful for you help. Here is what I have.
UPDATE [PMReporting_CostModel]
SET [PMReporting_CostModel].[month]=(SELECT MONTH(DATEADD(dd, @dateshift, CONVERT(datetime, CAST([PMReporting_CostModel].[month] AS varchar(2))+'/1/'+CAST([PMReporting_CostModel].[year] AS varchar(4)),126) AS CostModelDate)) FROM [PMReporting_CostMOdel] WHERE [PMReporting_CostModel].[projectId]=@projectId),
[PMReporting_CostModel].[year]=(SELECT YEAR(DATEADD(dd, @dateshift, CONVERT(datetime, CAST([PMReporting_CostModel].[month] AS varchar(2))+'/1/'+CAST([PMReporting_CostModel].[year] AS varchar(4)),126) AS CostModelDate)) FROM [PMReporting_CostMOdel] WHERE [PMReporting_CostModel].[projectId]=@projectId)
WHERE [PMReporting_CostModel].[projectId]=@projectId;
There can be many records with same projectId but each has a different month and year.
Thank you.
April 30, 2010 at 12:06 pm
Perhaps...?
UPDATE [PMReporting_CostModel]
SET [month]= MONTH(DATEADD(dd, @dateshift, CONVERT(datetime, CAST([month] AS varchar(2))+'/1/'+CAST([year] AS varchar(4)),126) AS CostModelDate)),
[year]= YEAR(DATEADD(dd, @dateshift, CONVERT(datetime, CAST([month] AS varchar(2))+'/1/'+CAST([year] AS varchar(4)),126) AS CostModelDate))
WHERE [projectId]=@projectId;
April 30, 2010 at 12:46 pm
I think that should work. It passes sql servers parser test.
Thank you for your help.
Ben
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply