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 CodeLink

 

One thought on “T-SQL equivalent for NEXT_DAY() of ORACLE

  1. Het is leuk om te doen ja, ook al moet ik soms wel een dag inhalen de dag erna ;-)Ik doe het vaak s aodvns, loop overdag niet met m’n camera rond. Zou wel leuk zijn als je er echt op uit gaat om een foto te maken voor de challenge:D

Feel free to leave a reply here...

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