Solved

Update SQL Date

Posted on 2014-01-09
10
389 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Rename SQL Instance/SQL Developer Edition 2012 2 19
export sql results to csv 6 35
SQL Help 27 45
How to debug a store procedure in MS SQL 2008? 3 10
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 …
Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

770 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