sql server: add business days to specified date, convert to weekday if end result is weekend

trying to add 30 business days to current date. if end date = current date +30 business days and end date falls on a saturday/sunday [after the addition of the 30 business days], then the end date should be the next monday, not saturday/sunday.

can you please help with this query in ms sql server

more ▼

asked Dec 13, 2010 at 05:05 PM in Default

avatar image

11 1 1 1

Current date + 30 days query works: Select Convert(Varchar(10),( DATEADD(Day,30,#DateDict.EndDate#)),101) as dt

Need help with part 2: - The 30 addition needs to be 30 business days - If the end date falls on a saturday/sunday, then it should reflect as the upcoming monday - Confusion is, if the end date is 30 business days, the end date should technically never fall on a weekend

Can u please help to add part 2 to the query provided above, thank you.

Dec 13, 2010 at 05:31 PM reshk

@reshk Not really, unless you mean something else. In your question, you ask to add 30 business days, but adding 30 to a given date does not really add 30 business days, but only between 20 and 22 depending on the input. You can check my answer and if it does what you need for a single input then you can restate it as a table valued function so you can use cross apply against your data.

Dec 13, 2010 at 05:36 PM Oleg

@Oleg: u r right, adding 30 does not make it business days and I am looking for business days which u rightly said is 42 days. Can u please help add to the query I have posted above. Sorry, am a bit new to this and appreciate your help, thanks.

Dec 13, 2010 at 05:41 PM reshk

@reshk What does #DateDict.EndDate# mean? It looks like some sort of VB code. Could you please provide some details, I can have a look when I get home later this evening. Can I assume that you have a table named DateDict which has a column EndDate? Are there any other columns of interest? Do you have to use the current value in the EndDate and select the value equal to the current value plus 30 business days?

Dec 13, 2010 at 05:49 PM Oleg

@Oleg: Yes, it is a table named datedict & field being referenced is endDate. I have an initial field startdate that is part of same table datedict. I enter a date into startdate field and enddate automatically gets populated with 30 business days from the date I entered. If 30 business days fall on a weekend, then enddate populates with the weekday monday. Thank you so much. Hope this makes sense.

Dec 13, 2010 at 06:06 PM reshk
show all comments (comments are locked)
10|1200 characters needed characters left

5 answers: sort voted first

If you just need to add 30 business days to a given variable of datetime data type then you can do something like this:

 declare @current_date datetime;
 declare @plus30_bisiness_days datetime;
 set @current_date = getDate();
 -- 30 business days is 6 weeks, which is 42 days
 with all_days (date_value) as
     select top 42
         dateadd(day, row_number() over 
             (order by [object_id]), @current_date)
         from sys.objects
     select -- the last record (or the max, same thing)
         @plus30_bisiness_days = max(date_value) 
         from all_days
         where datename(weekday, date_value) not like 'S%';
 -- check your result
 select @plus30_bisiness_days;

more ▼

answered Dec 13, 2010 at 05:31 PM

avatar image

20.6k 3 7 29

@Oleg - don't forget some countries have different weekdays and starting days of the week for religious reasons. We have offices in the middle east that have weekends on thursday and friday or friday and saturday. That is why I don't like the reliance of DATEFIRST - it is better to use an anchor date and work from there.

Love your implementation by the way, deserves more than the +1 I gave.

Dec 14, 2010 at 02:11 PM WilliamD

@WilliamD Thank you. You are correct, I just wanted to somehow justify the inevitable handicap of any solution utilizing the datepart function. In this specific case, it might work, but in reality, the definition of a business week is surely location-specific.

Dec 14, 2010 at 02:23 PM Oleg
(comments are locked)
10|1200 characters needed characters left

If you need a list of dates, change r = 30 to r

 DECLARE @Holidays TABLE (Holiday date)
 INSERT @Holidays
 SELECT '2010-12-24' UNION ALL
 SELECT '2010-12-31'
 SELECT [Date] 
        FROM(SELECT TOP 60 CAST(DATEADD(dd, ROW_NUMBER() OVER (ORDER BY [object_id]), GETDATE()) AS date)[Date]
             FROM master.sys.all_columns)d
             LEFT JOIN @Holidays h ON (h.Holiday = d.[Date])
             WHERE DATEPART(WEEKDAY,[Date]) NOT IN (1,7)
             AND Holiday IS NULL)e
 WHERE r = 30
more ▼

answered Dec 13, 2010 at 05:59 PM

avatar image

Scot Hauder
6.5k 13 16 22

(comments are locked)
10|1200 characters needed characters left

OK, assuming you have a numbers table then this will add 30 days to a date and if that results in a Sat or Sun then will move to the next Mon:

  -- sets Monday = 1 and Sunday = 7
     GETDATE() ,
     CASE WHEN DATEPART(dw, GETDATE() + n.[Number]) = 6
          THEN GETDATE() + [n].[Number] + 2
          WHEN DATEPART(dw, GETDATE() + n.[Number]) = 7
          THEN GETDATE() + [n].[Number] + 1
          ELSE GETDATE() + [n].[Number]
     END AS [30 days]
     [dbo].[numbers] AS n
     [n].[Number] <= 30

The example needs the numbers table to show the 'step' over the weekends, you will simply need to add 30 to your date.

OK, your comment says it doesnt work. Can you try this code on the Adventureworks database and then let us know the error you have:

 USE [adventureworks]
 [soh].[OrderDate] ,
 CASE WHEN DATEPART(dw, [soh].[OrderDate] + 30) = 6
      THEN [soh].[OrderDate] + 30 + 2
      WHEN DATEPART(dw, [soh].[OrderDate] + 30) = 7
      THEN [soh].[OrderDate] + 30+ 1
      ELSE [soh].[OrderDate] + 30
 END AS [30 days]
 [Sales].[SalesOrderHeader] AS soh
 GROUP BY [soh].[OrderDate]
 ORDER BY [soh].[OrderDate]
more ▼

answered Dec 14, 2010 at 01:21 AM

avatar image

Fatherjack ♦♦
43.8k 79 102 118

Unfortunately, this did not work. Any other suggestions please?

Dec 14, 2010 at 06:48 AM reshk

in what way did it not work? what is the table name and column name of the date column you want to work with?

Dec 14, 2010 at 06:54 AM Fatherjack ♦♦

@Fatherjack I believe the reason it does not work is that your script adds 30 days and then optionally adds one or two to make the weekend to go over to Monday, but the question is asking to add 30 business days not 30 days :)

Dec 14, 2010 at 07:45 AM Oleg

OK, how do you define business days - Mon to Sat? Bank Hols to be incl or excl? Are you actually needing to apply a business rule of adding 6 weeks to a date?

Dec 14, 2010 at 08:22 AM Fatherjack ♦♦

@Fatherjack I believe that @reshk wants 30 business days added to a given date. Business days mean Monday through Friday (5 days a week) and from what I read in the comments so far @reshk is not looking to account for holidays at this time. My answer does the trick for a given value, but there is no telling what is the actual requirement from the question definition. I asked @reshk to provide some details, because to have a default value for the end date column based on the start date column value is pure evil (one has to somehow force the devs NOT TO PROVIDE the value for inserts), and to have a virtual computed column is heavy. It looks like it is a wait and see for now.

Dec 14, 2010 at 08:57 AM Oleg
(comments are locked)
10|1200 characters needed characters left

OK, a new answer just to separate this from my previous one but I am thinking that there is a strong justification for a calendar table for this solution. One that permanently identifies dates and their business significance - whether its a working day or a bank holiday or any other important fact. Again using a numbers table to generate the content makes it really easy...

 USE [adventureworks]
 --    drop table calendardata
 CREATE TABLE CalendarData
       D_Date DATE ,
       Is_Working_Day BIT DEFAULT ( 0 ) ,
       Is_BankHoliday BIT DEFAULT ( 0 ) ,
       D_Name CHAR(10)
 INSERT  INTO [dbo].[CalendarData]
         ( [D_Date] ,
           [Is_Working_Day] ,
           [Is_BankHoliday] ,
             CONVERT(DATE, GETDATE() + ( number - 1 )) ,
             CASE WHEN DATEPART(weekday, GETDATE() + ( number - 1 )) IN ( 7, 1 )
                  THEN 0
                  ELSE 1
             END ,
             0 ,
             DATENAME(weekday, GETDATE() + ( number - 1 ))
             [dbo].[numbers] AS n
             [n].[Number] < 3650
 -- this means that we can simply count 30 working days like this ...
     d_date ,
     [dbo].[CalendarData] AS cd
     [cd].[Is_Working_Day] = 1

Joining to this table from wherever its necessary across the database will bring consistent results and holidays etc (eg Royal Weddings etc) can easily be added to the data here and it will automatically adjust as needed.

more ▼

answered Dec 15, 2010 at 01:46 AM

avatar image

Fatherjack ♦♦
43.8k 79 102 118

(comments are locked)
10|1200 characters needed characters left

The shortest way I have found to add business days is:

CREATE FUNCTION [dbo].[AddBusinessDays] (@Date date,@n INT) RETURNS DATE AS BEGIN DECLARE @d INT;SET @d=4-SIGN(@n)*(4-DATEPART(DW,@Date));RETURN DATEADD(D,@n+((ABS(@n)+@d-2)/5)*2*SIGN(@n)-@d/7,@Date) END

This works with any size date range including negative. Also works with starting on weekends. No loops, no tables, no case, no problem. Enjoy.

more ▼

answered Feb 12, 2014 at 09:38 PM

avatar image

1 1

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Dec 13, 2010 at 05:05 PM

Seen: 12512 times

Last Updated: Feb 13, 2014 at 03:24 PM

Copyright 2018 Redgate Software. Privacy Policy