Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Update SQL Date

Posted on 2014-01-09
10
Medium Priority
?
402 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
[X]
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
  • 5
  • 4
10 Comments
 
LVL 66

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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

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 1200 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

670 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