May 22, 2011 at 5:40 am
Can someone tell me why my statements are adding days instead of weekdays? The returns on all of the variables and subselects are int, I have tried several different options all of which parse and run but add day including Sat and Sun. I have tried month and year as well to test but still adding 'days' only
set @sh_act_rfi_date = (select DATEADD(dw, (select sh_act_rfi_days from sh_procurement_days), @date_approved));
set @sh_plan_rfi_date = (select DATEADD (dw, @sh_act_rfi_days, @date_approved));
set @sh_act_bid_list_app_date = (select DATEADD (dw,(select sh_act_bid_list_app_days from sh_procurement_days), @sh_act_rfi_date));
set @sh_act_proposal_issue_date = (select DATEADD (dw,SUM(@sh_act_bid_list_app_days+@sh_act_rfi_days+@sh_act_proposal_issue_days), @date_approved));
Thank you!
May 22, 2011 at 6:10 am
kimberlyphillips01 (5/22/2011)
Can someone tell me why my statements are adding days instead of weekdays? The returns on all of the variables and subselects are int, I have tried several different options all of which parse and run but add day including Sat and Sun. I have tried month and year as well to test but still adding 'days' onlyset @sh_act_rfi_date = (select DATEADD(dw, (select sh_act_rfi_days from sh_procurement_days), @date_approved));
set @sh_plan_rfi_date = (select DATEADD (dw, @sh_act_rfi_days, @date_approved));
set @sh_act_bid_list_app_date = (select DATEADD (dw,(select sh_act_bid_list_app_days from sh_procurement_days), @sh_act_rfi_date));
set @sh_act_proposal_issue_date = (select DATEADD (dw,SUM(@sh_act_bid_list_app_days+@sh_act_rfi_days+@sh_act_proposal_issue_days), @date_approved));
Thank you!
dw,dayofyear, and day return the same value. If I understand your objective correctly, you were trying to add #of business days instead of #of days. The following might help to achieve your goal:
select @sh_act_rfi_date = DATEADD(day,CONVERT(INT,sh_act_rfi_days/5)*7 + sh_act_rfi_days%5, @date_approved) from sh_procurement_days
May 22, 2011 at 7:32 am
Thank you for the reply, I greatly appreciate it. Your solution parses and executes but returns no date at all. any additional ideas?
May 22, 2011 at 7:59 am
kimberlyphillips01 (5/22/2011)
Thank you for the reply, I greatly appreciate it. Your solution parses and executes but returns no date at all. any additional ideas?
Please check your statement can actually return a sh_act_rfi_days value before doing the calculation. I suspect it's a problem with the sh_act_rfi_days value. You may try replace the variables with some static figures and test the results.
May 22, 2011 at 8:39 am
This is my set statement on the rfi days, it is as basic as it gets and works adding all days perfectly.
set @sh_act_rfi_days = (select sh_act_rfi_days from sh_procurement_days);
Maybe I am not inserting your solution correctly. I tried it several ways including alone but I need to set the variable and came up with the following
set @sh_act_rfi_date = (select DATEADD(day,CONVERT(INT,sh_act_rfi_days/5)*7 + sh_act_rfi_days%5, @date_approved) from sh_procurement_days);
Thanks again for the help
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply