Solved

Update SQL Date

Posted on 2014-01-09
10
391 Views
Last Modified: 2014-01-09
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
0
Comment
Question by:W.E.B
  • 5
  • 4
10 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39769651
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
 

Author Comment

by:W.E.B
ID: 39769664
hello,
business day is M-T-W-T-F (no matter if it is a holiday or not)
thanks,
0
 
LVL 41

Expert Comment

by:Sharath
ID: 39769775
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

Author Comment

by:W.E.B
ID: 39769820
Hello,
thanks,
This works for January,
what about the rest of the months?

Thanks
0
 
LVL 41

Expert Comment

by:Sharath
ID: 39769852
Do you want to update all jan records with 6th workday of Jan, feb records with 6th workday of Feb and so on..?
0
 

Author Comment

by:W.E.B
ID: 39769858
that is exactly it.

thanks for your help.
0
 
LVL 41

Expert Comment

by:Sharath
ID: 39769866
How many years worth data you have? 1 year?
0
 

Author Comment

by:W.E.B
ID: 39769876
correct,
1 year.
0
 
LVL 41

Accepted Solution

by:
Sharath earned 300 total points
ID: 39769924
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
 

Author Closing Comment

by:W.E.B
ID: 39769957
super,
thank you.
0

Featured Post

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question