T-SQL equivalent for NEXT_DAY() of ORACLE

I thought of creating a simple function for this operation, in T-SQL.

Function Name: Next_Day

Input Params: @FromDate – Date from which you wish to get the next day; @DayNumber – Day of week for which you wish to get the next day (6=>Saturday, 7=>Sunday)

Operation: SELECT dbo.Next_Day(GETDATE(),6) //Returns the next Saturday (6) from today

Function: Function body

CREATE FUNCTION dbo.Next_Day
(
@FromDate DATETIME,
@DayNumber INT
)
RETURNS DATETIME
AS
BEGIN

DECLARE @CurrentDate AS DATETIME
DECLARE @DayOfWeek AS TINYINT

SELECT @DayOfWeek = DATEPART(WEEKDAY, @FromDate)
SELECT @DayNumber = @DayNumber + 1

IF (@DayOfWeek < @DayNumber) — Not equal to Thursday
BEGIN
SELECT @CurrentDate = (@FromDate + (@DayNumber-@DayOfWeek))
END
ELSE
BEGIN
SELECT @CurrentDate = (@FromDate + ((@DayNumber+2)-(@DayOfWeek-@DayNumber)))
END

RETURN @CurrentDate
END

GO

–SELECT dbo.Next_Day(‘2011-04-05’,6) //2011-04-09 11:46:38.867 – next saturday after 5th April 2011

Run the above SQL statement and you get your function.

Reference Code: Link

Published by aackose

A Sitecore & SharePoint Consultant with over 11 years of extensive technical experience in UI/UX Designing, Requirements Analysis, Designing, Developing, Testing, Deployment, Infrastructure Setup for web/enterprise-based applications using Microsoft Technologies (SharePoint, Sitecore & .NET) across all phases of SDLC View all posts by aackose