How to get Saturday's Date (Or any other weekday's Date)- SQL Server

Question

How to get Saturday's Date. I have today's date with me.

GETDATE()

How to do this.

For eg. TODAY is 08-08-2011

I want output as 08-13-2011

Answer 1

This is a function that will return the next Saturday if you call it like this:

SELECT dbo.fn_Get_NextWeekDay('2011-08-08', 6)

The "6" comes from the list of possible values you can set for DATEFIRST.

You can get any other day of the week by changing the second parameter accordingly.

This is the function:

IF OBJECT_ID('dbo.fn_Get_NextWeekDay') IS NOT NULL 
  DROP FUNCTION dbo.fn_Get_NextWeekDay
GO
CREATE FUNCTION dbo.fn_Get_NextWeekDay(
     @aDate   DATETIME
   , @dayofweek      INT
    /*
      @dw - day of the week
      1 - Monday
      2 - Tuesday
      3 - Wednesday
      4 - Thursday
      5 - Friday
      6 - Saturday
      7 - Sunday
    */   
  )
RETURNS DATETIME 
AS
/*
  SELECT dbo.fn_Get_NextWeekDay('2011-08-08', 6)
  SELECT dbo.fn_Get_NextWeekDay('2011-08-08', 1)
*/
BEGIN
  RETURN 
      DATEADD(day
        , ( @dayofweek + 8 - DATEPART(dw, @aDate) - @@DATEFIRST ) % 7
        , @aDate 
      )  
END
GO

[EDIT] This might be another solution. This should work in any language:

IF OBJECT_ID('dbo.fn_NextWeekDay') IS NOT NULL 
  DROP FUNCTION dbo.fn_NextWeekDay
GO
CREATE FUNCTION dbo.fn_NextWeekDay(
     @aDate     DATE
   , @dayofweek NVARCHAR(30)
  )
RETURNS DATE
AS
/*
  SELECT dbo.fn_NextWeekDay('2016-12-14', 'fri')
  SELECT dbo.fn_NextWeekDay('2016-03-15', 'mon')
*/
BEGIN
  DECLARE @dx INT = 6
  WHILE UPPER(DATENAME(weekday,@aDate)) NOT LIKE UPPER(@dayofweek) + '%'
  BEGIN

    SET @aDate = DATEADD(day,1,@aDate)

    SET @dx=@dx-1
    if @dx < 0 
    BEGIN
      SET @aDate = NULL 
      BREAK
    END
  END

  RETURN @aDate

END
GO