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

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

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

Use DATEPART to get the day of week of today and add the difference to the desired day of week to todays date.

DECLARE @Today date = 'TODAYS-DATE';
DECLARE @TodayNumber int = DATEPART(dw, @Today) -- Get the day number
DECLARE @Saturday date = DATEADD(DAY, (6-@TodayNumber)%7, @Today) 
-- Add the number of days between today and saturday (the 6th day), modulus 7 to stop you adding negative days

Hope that helps!

Use a Calendar table (table with one row per date):

SELECT MIN(DateValue) DateValue
FROM Calendar
WHERE DateValue >= CURRENT_TIMESTAMP
AND DayOfWeek = 'Saturday';

Another approach to this takes two steps, but might be more readable (look ma, no modulus):

1. Go back to last saturday: DATEADD(DAY, -1 * datepart(weekday, GETDATE()), getdate())
2. Then, add on a week: DATEADD(WEEK, 1, @lastSaturday, getdate()))

The whole thing:

declare @today DATETIME = GETDATE()
declare @lastSaturday DATETIME = DATEADD(DAY, -1 * datepart(weekday, @today), @today)
declare @nextSaturday DATETIME = DATEADD(WEEK, 1, @lastSaturday)

Or, if you're ok with @today being GETDATE(), you can do the calculation all at once:

SELECT DATEADD(WEEK, 1, DATEADD(DAY, -1 * datepart(weekday, GETDATE()), getdate()))

Checkout the SQL DATEADD function.

DATEADD (Transact-SQL)

Which you can use this along with DATEPART function to return the correct date.

DATEPART (Transact-SQL)

Try this :

SET DATEFIRST 7
DECLARE @d DATETIME
SET @d = '2011-08-08' --GETDATE()

SELECT NEXT_SAT = DATEADD(day, (7  + @@DATEFIRST - DATEPART(dw, @d)) % 7, @d )

declare @Curdate date=( SELECT SWITCHOFFSET(SYSDATETIMEOFFSET(),'+05:30') )  
declare @nextsaturdaydate date=(select dateadd(d, 7-datepart(WEEKDAY, @CurDate),@Curdate))
select @nextsaturdaydate