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)
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;
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
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