You must Sign In to post a response.
  • Category: SQL Server

    Next Business Day in given date range - SQL Server

    How to find the Next Business Day Recursively (Excluding week ends and holidays) using SQL.
  • #762381
    Using Recursion we can get the next business days and also we can exclude the weekends. To exclude the holidays you can add holidays date in a table or in a temp table and can exclude it with a filter in the below SQL


    WITH Base(BusinessDays)
    AS
    (
    select CAST(CAST(GETDATE() AS DATE) AS DATETIME)
    UNION ALL
    SELECT CAST(BusinessDays AS DATETIME) + 1
    FROM Base
    WHERE YEAR(CAST(BusinessDays AS DATETIME) + 1) = 2015
    )
    select * from base
    WHERE DATEPART(DW, BusinessDays) NOT IN (1,7) option(MAXRECURSION 0)

    Please mark this as Answer, if this helps

    Regards,
    Alwyn Duraisingh.M 
    << Database Administrator >>
    Jesus saves! The rest of us better make backups...

  • #762385
    Hi

    Alwyn

    Good post I am also learning this Query.

    I have change single character in your query for sunday only holiday so we no need show the sunday date means use this query .




    WITH Base(BusinessDays)
    AS
    (
    select CAST(CAST(GETDATE() AS DATE) AS DATETIME)
    UNION ALL
    SELECT CAST(BusinessDays AS DATETIME) + 1
    FROM Base
    WHERE YEAR(CAST(BusinessDays AS DATETIME) + 1) = 2015
    )
    select * from base
    WHERE DATEPART(DW, BusinessDays) NOT IN (1,6) option(MAXRECURSION 0)


    Name : Dotnet Developer-2015
    Email Id :kumaraspcode2009@gmail.com

    'Not by might nor by power, but by my Spirit,' says the LORD Almighty.


Sign In to post your comments