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
W.E.BAsked:
Who is Participating?
 
SharathData EngineerCommented:
check this query
you can change the 6 to whatever number you want.
DECLARE @workDays INT 
SET @workDays = 6; 

;WITH CTE 
     AS (SELECT CONVERT(DATE, '1/1/2014')  StartDate, 
                CONVERT(DATE, '12/1/2014') EndDate 
         UNION ALL 
         SELECT DATEADD(MM, 1, StartDate), 
                EndDate 
           FROM CTE 
          WHERE DATEADD(mm, 1, StartDate) <= EndDate), 
     CTE2 
     AS (SELECT StartDate, 
                ( @workDays / 5 * 7 + @workDays % 5 ) - 1 AS calDays 
           FROM CTE), 
     CTE3 
     AS (SELECT StartDate, 
                calDays + CASE DATEPART(dw, DATEADD(dd, calDays, StartDate)) WHEN 1 THEN 1 WHEN 7 THEN 2 ELSE 0 END AS calDays
           FROM CTE2) 
UPDATE T1 
   SET T1.NextRunDate = DATEADD(dd, T2.calDays, T2.StartDate) 
  FROM schedules T1 
       JOIN CTE3 T2 
         ON CONVERT(VARCHAR(7), T1.NextRunDate, 102) = CONVERT(VARCHAR(7), T2.StartDate, 102) 

Open in new window

just checked the next 6th workday for each month and its looking good.
StartDate	NextRunDate
2014-01-01	2014-01-08
2014-02-01	2014-02-10
2014-03-01	2014-03-10
2014-04-01	2014-04-08
2014-05-01	2014-05-08
2014-06-01	2014-06-09
2014-07-01	2014-07-08
2014-08-01	2014-08-08
2014-09-01	2014-09-08
2014-10-01	2014-10-08
2014-11-01	2014-11-10
2014-12-01	2014-12-08

Open in new window


http://sqlfiddle.com/#!3/58587/2
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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.
0
 
W.E.BAuthor Commented:
hello,
business day is M-T-W-T-F (no matter if it is a holiday or not)
thanks,
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
SharathData EngineerCommented:
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

0
 
W.E.BAuthor Commented:
Hello,
thanks,
This works for January,
what about the rest of the months?

Thanks
0
 
SharathData EngineerCommented:
Do you want to update all jan records with 6th workday of Jan, feb records with 6th workday of Feb and so on..?
0
 
W.E.BAuthor Commented:
that is exactly it.

thanks for your help.
0
 
SharathData EngineerCommented:
How many years worth data you have? 1 year?
0
 
W.E.BAuthor Commented:
correct,
1 year.
0
 
W.E.BAuthor Commented:
super,
thank you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.