Blog Post

T-SQL – How to find Next Business day from a given date

,

There are many methods to find next business day. One common way is to use of calendar table.  This post is to get an output using T-SQL Or UDF function.

Download T-SQL  NextBusinessDay

The details are given below

The script can be executed by feeding Input values to SQL. In the below example @dt parameter accepts input value.

DECLARE @dt datetime='20150113'
SELECT DATEADD(dd,CASE WHEN DATEDIFF(dd,0,@dt)%7 > 3 THEN 7-DATEDIFF(dd,0,@dt)%7 ELSE 1 END,@dt)

NextWorkingDay1

The following methods the input is fed from Table.

Method 1: T-SQL

Declare @TestDate Table(MyDate date);
Insert @TestDate(MyDate) Values('20150112'), ('20150113'), ('20150114'), ('20150115'), ('20150116'), ('20150117'), ('20150118');
Select MyDate, DateAdd(day, Case DateDiff(day, '19000101', MyDate) % 7 When 4 Then 3 When 5 Then 2 Else 1 End, MyDate) As NextWeekDay
From @TestDate;

NextWorkingDay2

Method 2: T-SQL

Declare @TestDate Table(MyDate date);
Insert @TestDate(MyDate) Values('20150112'), ('20150113'), ('20150114'), ('20150115'), ('20150116'), ('20150117'), ('20150118');
SELECT MyDate,DATEADD(dd,CASE WHEN DATEDIFF(dd,0,MyDate)%7 > 3 THEN 7-DATEDIFF(dd,0,MyDate)%7 ELSE 1 END,MyDate) from @TestDate

NextWorkingDay3

Method 3 : UDF

CREATE FUNCTION dbo.UF_GetWorkingDay(@givenDate DATETIME)
RETURNS DATETIME
AS
BEGIN
 DECLARE @workingDate DATETIME
 IF (DATENAME(dw , @givenDate) = 'Friday')
 BEGIN
 SET @workingDate = DATEADD(day, 3, @givenDate)
 END
 ELSE IF (DATENAME(dw , @givenDate) = 'Saturday')
 BEGIN
 SET @workingDate = DATEADD(day, 2, @givenDate)
 END
 ELSE 
 BEGIN
 SET @workingDate = DATEADD(day, 1, @givenDate)
 END
 RETURN @workingDate
END
Declare @TestDate Table(MyDate date);
Insert @TestDate(MyDate) Values('20150112'), ('20150113'), ('20150114'), ('20150115'), ('20150116'), ('20150117'), ('20150118');
select MyDate,dbo.UF_GetWorkingDay(Mydate) from @TestDate

NextWorkingDay4

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating