Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Update SQL Date

Posted on 2014-01-09
10
390 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 40

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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Comment

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

Thanks
0
 
LVL 40

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 40

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 40

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
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.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

856 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