Pages

Saturday, May 7, 2022

How to get next business day after excluding weekends in SQL

 Below query is helpful to find next business date by excluding weekends. We can use  same logic to find out the weekend.

1. Find next business day excluding weekend holiday.

   DECLARE @Current_date DATETIME = '14 Nov 2020'

   SELECT CASE

   WHEN (((DATEPART(DW,  @Current_date) - 1 ) + @@DATEFIRST ) % 7) IN (6)

           THEN @Current_date + 2

   WHEN (((DATEPART(DW,  @Current_date) - 1 ) + @@DATEFIRST ) % 7) IN (5)

           THEN @Current_date + 3

   ELSE @Current_date + 1 END AS next_business_date

2. Check today is business day or not in SQL server.

    SELECT (CASE

        WHEN (((DATEPART(DW,  GETDATE()) - 1 ) + @@DATEFIRST ) % 7) IN (0,6)

        THEN 0

        ELSE 1

      END) AS is_business_day