Update SQL Date

Hello,
can you please help,
I need to update a lot of orders dates to a specific business date of the month.

Example
6th Business day in Jan 2014 is Wednesday 8th, 2014
NextRunDate = '2014-01-08 05:00:00.000'

samples
UPdate schedules set NextRunDate = '6th Business day of January' where ScheduleID = 1704
UPdate schedules set NextRunDate = '6th Business day of Feb' where ScheduleID = 1706
UPdate schedules set NextRunDate = '6th Business day of March' where ScheduleID = 1752
UPdate schedules set NextRunDate = '6th Business day of April' where ScheduleID = 1753
UPdate schedules set NextRunDate = '6th Business day of May' where ScheduleID = 1754
UPdate schedules set NextRunDate = '6th Business day of June' where ScheduleID = 1755
UPdate schedules set NextRunDate = '6th Business day of July' where ScheduleID = 1756
UPdate schedules set NextRunDate = '6th Business day of August' where ScheduleID = 1757
UPdate schedules set NextRunDate = '6th Business day of Sept' where ScheduleID = 1759
UPdate schedules set NextRunDate = '6th Business day of Oct' where ScheduleID = 1761
UPdate schedules set NextRunDate = '6th Business day of Nov' where ScheduleID = 1762
UPdate schedules set NextRunDate = '6th Business day of Dec' where ScheduleID = 1763

Your help is appreciated.
thanks

Microsoft SQL Server 2008

Last Comment

W.E.B

8/22/2022 - Mon

Jim Horn

1/9/2014

In your business / country / world how do you define '6th business day'?
Reason I ask is because SQL Server does not have a 'business day' function, and everyplace is different. National holidays, state holidays, Green Bay Packers football games, Dave's birthday, special events, etc.

I have an article out there on How to build a SQL Calendar Table, that will enable you to have a table of days, and specifically define what's a business day and what's not, so that you can pull off a 'give me the 6th business day' and be guaranteed of success.

Good luck.

W.E.B

1/9/2014

ASKER

hello,
business day is M-T-W-T-F (no matter if it is a holiday or not)
thanks,

Sharath S

1/9/2014

check this.

DECLARE @workDays int, @calDays int,@NextRunDate datetime
SET @workDays = 6
SELECT @calDays = @workDays / 5 * 7 + @workDays % 5
WHILE DatePart(dw, DateAdd(dd, @calDays, '01/01/2014')) IN (7, 1)
  SET @calDays = @calDays + 1
SELECT @NextRunDate = DateAdd(dd, @calDays-1, '01/01/2014') 

Update schedules set NextRunDate = @NextRunDate 
-- Add your WHERE condition if needed.

Open in new window

All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat

William Peck

W.E.B

1/9/2014

ASKER

Hello,
thanks,
This works for January,
what about the rest of the months?

Thanks

Sharath S

1/9/2014

Do you want to update all jan records with 6th workday of Jan, feb records with 6th workday of Feb and so on..?

W.E.B

1/9/2014