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

Solution 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

Solution 2

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

Solution 3

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

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

Solution 4

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!

Solution 5

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()))

View more solutions

Comments

[Sreekumar P]

but SET DATEFIRST 7 this cannot be used inside a SQL Function.. wht to do ?

[leoinfo]

what's your current value of the SET DATEFIRST ? ( select @@DATEFIRST )

[Sreekumar P]

current value is 7 . but it might change from SYSTEM to SYSTEM ..right ??

[leoinfo]

that's true, but you usually do not mess around with that value server-wide

[Sreekumar P]

yes... I got solution give this `SET DATEFIRST 7' line before that function is been called...mostly from the SP. :)

[leoinfo]

Check my other answer for a more generic solution ( stackoverflow.com/questions/6982061/… )

[Sreekumar P]

@leoinfo let us continue this discussion in chat

[Ronen Ariely]

This is great solution but is non-deterministic and will not work in any language.

[Ronen Ariely]

For the value dbo.fn_Get_NextWeekDay('2016-12-14', 2) the function returns 2016-12-13 00:00:00.000 in some configurations

[Christopher Krah]

Not sure, but I think this would not work if today's date is a sunday. EDIT: Never mind, turns out -1%7 yields -1 and not 1 as I expected.