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

Advertisements

About Prashanth Jayaram

DB Technologist, Author, Blogger, Operations-Mgr at CTS, Automation Expert, Technet WIKI Ninja, MVB and Powershell Geek You can connect me via https://social.technet.microsoft.com/profile/prashanth jayaram/ http://www.sqlshack.com/author/prashanth/ https://www.tumblr.com/blog/prashantjayaram http://www.sqlservercentral.com/blogs/powersql-by-prashanth-jayaram/ http://www.toadworld.com/members/prashanthjayaram/ My Articles are published in following sites http://www.ssas-info.com/analysis-services-articles/ http://db-pub.com/ http://www.sswug.org/sswugresearch/community/
This entry was posted in T-SQL and tagged , , , , , , , . Bookmark the permalink.

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

  1. Adi says:

    This is nice. However, not perfect in the real world, in 99% on the cases there are public holidays which don’t fall on a weekend day, so we have to have a table holding the public holidays.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s