x

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

reshk gravatar image

reshk
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
(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;

<!-- Begin Edit

The statement above does not really do the trick for what the question actually asks. There are many ways to implement desired requirement, and I would like to add and discuss a couple of methods. Of course, as Scot Hauder already noted, the best way to handle the situations when computed / default values are required and calculated is to deprive the users from the insert rights on the table and then use the stored procedure to actually insert the data. Let the procedure handle all the logic required to insert desired data and grant users execute permission on the proc.

The logic to get the date value offset by 30 business days is "***borrowed***" from Fatherjack's answer with minor adjustments, because the query in his answer happens to be the best. The idea is very simple: 30 business days (disregarding any holidays) equates to 6 weeks or 42 days. So for every date which is between Monday and Friday, adding 42 days does the trick. Saturday requires addition of 44 and Sunday - 43 days instead of 42 to advance the result to coming Monday. The dateadd - datediff logic is used to strip the time part from the final output (this is much better then using convert). Of course it goes without saying that the samples below assume that the first day of the week is Sunday. This is simply because it is Sunday. Whoever invented to consider any other day the first day of the week is simply wrong IMHO.

-- First example simply using a table with nullable
--  column and select statement to view desired data
create table dbo.DateDict
(
    RecordID int not null identity (1, 1)
        constraint PK_DateDict primary key clustered,
    StartDate datetime not null,
    EndDate datetime null
);

-- insert 1000 records in it (all rows will have null for EndDate)
insert into dbo.DateDict (StartDate)
select top 1000
    dateadd(day, row_number() over (order by [object_id]), getDate())
    from sys.all_columns;

-- select data to show EndDate as 30 business days after 
-- StartDate honouring the rule to advance to coming Monday
-- should the StartDate value fall on Saturday or Sunday
select 
    RecordID, StartDate,
    EndDate = dateadd(day, 
        case datepart(weekday, StartDate)
            when 1 then 43 
            when 7 then 44 
        else 42
        end, datediff(day, 0, StartDate))
    from dbo.DateDict;
go

The example above does not use any computed column, just a select statement to show desired data. Here is another example, now showing how to use the computed column instead.

-- Second example showing the implementation of the virtual
-- computed column in place of the original EndDate datetime
alter table dbo.DateDict drop column EndDate;
go

-- add new computed column in place of the one just dropped
alter table dbo.DateDict
    add EndDate as dateadd(day, 
        case datepart(weekday, StartDate)
            when 1 then 43
            when 7 then 44
            else 42
        end, datediff(day, 0, StartDate));
go

-- now use the select * to view computed data
select * from dbo.DateDict;

-- the simple select above shows data just like the 
-- original select which was used before the table was 
-- altered to use computed column
RecordID    StartDate               EndDate
----------- ----------------------- -----------------------
1           2010-12-15 14:26:47.940 2011-01-26 00:00:00.000
2           2010-12-16 14:26:47.940 2011-01-27 00:00:00.000
3           2010-12-17 14:26:47.940 2011-01-28 00:00:00.000
4           2010-12-18 14:26:47.940 2011-01-31 00:00:00.000
5           2010-12-19 14:26:47.940 2011-01-31 00:00:00.000

-- etc

What is the major shortfall of any of the above examples? They both use some logic to calculate desired output, but the data in the EndDate column is not persisted. Unfortunatley, it is not possible to easily trick the engine that the calculation logic is deterministic (always returns the same output for the same input). Since the engine believes that the column data is not deterministic, it is not possible to make it persisted. So, while the solution above will work, it is clearly not the best design, which would make the data in the EndDate column simply hardened rather than logically calculated every time.

End Edit-->
more ▼

answered Dec 13, 2010 at 05:31 PM

Oleg gravatar image

Oleg
15.9k 2 4 24

@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 <=30

DECLARE @Holidays TABLE (Holiday date)
INSERT @Holidays
SELECT '2010-12-24' UNION ALL
SELECT '2010-12-31'

SELECT [Date] 
FROM  (SELECT [Date], r = ROW_NUMBER() OVER (ORDER BY (SELECT 1)) 
       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

Scot Hauder gravatar image

Scot Hauder
6k 13 15 18

(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:

SET DATEFIRST 1
 -- sets Monday = 1 and Sunday = 7
SELECT
    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]
FROM
    [dbo].[numbers] AS n
WHERE
    [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.

[Edit]
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]
GO
SELECT
[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]
FROM
[Sales].[SalesOrderHeader] AS soh
GROUP BY [soh].[OrderDate]
ORDER BY [soh].[OrderDate]
more ▼

answered Dec 14, 2010 at 01:21 AM

Fatherjack gravatar image

Fatherjack ♦♦
42.3k 75 78 108

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]
GO
--  drop table calendardata
CREATE TABLE CalendarData
    (
      D_Date DATE ,
      Is_Working_Day BIT DEFAULT ( 0 ) ,
      Is_BankHoliday BIT DEFAULT ( 0 ) ,
      D_Name CHAR(10)
    )
go 
INSERT  INTO [dbo].[CalendarData]
        ( [D_Date] ,
          [Is_Working_Day] ,
          [Is_BankHoliday] ,
          [D_Name]

        )
        SELECT
            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 ))
        FROM
            [dbo].[numbers] AS n
        WHERE
            [n].[Number] < 3650
go 
-- this means that we can simply count 30 working days like this ...
SELECT TOP 30
    d_date ,
    [cd].[D_Name]
FROM
    [dbo].[CalendarData] AS cd
WHERE
    [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

Fatherjack gravatar image

Fatherjack ♦♦
42.3k 75 78 108

(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 at 09:38 PM

ElmerMiller gravatar image

ElmerMiller
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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x1834

asked: Dec 13, 2010 at 05:05 PM

Seen: 6521 times

Last Updated: Feb 13 at 03:24 PM