-
How to get next Date excluding weekends with start date and the number of working days using SQL ?
almost 10 years ago
almost 10 years ago
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
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)