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

 

  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

    Reply

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 )

Google+ photo

You are commenting using your Google+ 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 )

w

Connecting to %s

Advertisements
Advertisements
%d bloggers like this: