Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • How to get next Date excluding weekends with start date and the number of working days using SQL ?

    • 0
    • 1
    • 1
    • 1
    • 0
    • 0
    • 0
    • 0
    • 282
    Comment on it

    Hi All,
    While working with SQL, I came across the requirement to get the business days only. I had the number of working days and the starting date. Now the requirement was to get the next date by excluding all the Saturday Sunday and only to count the week days. So for this, we can create a common function which will return the date of next working day. Below is the function I have used to resolve my issue, hope it will help you too.

    USE [dbo.TestDB]
    GO
    /****** Object:  UserDefinedFunction [dbo].[add_business_days]    Script Date: 5/8/2015 4:40:20 PM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE Function [dbo].[add_business_days]
    (
        @start_date date,
        @no_of_days numeric
    )
    RETURNS date
    AS
    BEGIN
        Declare @counter numeric;
        Declare @new_date date;
        Declare @day_number numeric;
    
        set @counter = 1;
        set @new_date = @start_date;
        /* Loop to determine how many days to add */
       while @counter <= @no_of_days
       BEGIN   
          /* Add a day */
          set @new_date = DATEADD(DAY, 1, @new_date);
          set @day_number = datepart(dw,@new_date);
    
          /* Increment counter if day falls between Monday to Friday */
          if (@day_number >= 2 and @day_number <= 6)
            BEGIN
                set @counter= @counter + 1;
            END
       END
       RETURN @new_date; 
    END
    

    Happy Coding...
    CHEERS!!!

 0 Comment(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: