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
DECLARE @CurrentDate AS DATETIME
DECLARE @DayOfWeek AS TINYINT
SELECT @DayOfWeek = DATEPART(WEEKDAY, @FromDate)
SELECT @DayNumber = @DayNumber + 1
IF (@DayOfWeek < @DayNumber) — Not equal to Thursday
SELECT @CurrentDate = (@FromDate + (@DayNumber-@DayOfWeek))
SELECT @CurrentDate = (@FromDate + ((@DayNumber+2)-(@DayOfWeek-@DayNumber)))
–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