How to find the nearest (day of the week) on a specific date

Question

I dealt with inquiries, and my current scenario is to find the next Saturday for a specific date. After I got the logic, I came up with what looks like a long and dirty query. And I was wondering if there is a way to simplify this. Here is my request

DECLARE @DATE DATE SET @DATE ='2013-09-13' IF DATENAME(DW,@DATE) = 'SUNDAY' BEGIN SELECT DATEADD(DAY,-1,@DATE) AS DATE, 'IS THE NEAREST SATURDAY' END ELSE IF DATENAME(DW,@DATE) = 'MONDAY' BEGIN SELECT DATEADD(DAY,-2,@DATE) AS DATE, 'IS THE NEAREST SATURDAY' END ELSE IF DATENAME(DW,@DATE) = 'TUESDAY' BEGIN SELECT DATEADD(DAY,-3,@DATE) AS DATE, 'IS THE NEAREST SATURDAY' END ELSE IF DATENAME(DW,@DATE) = 'WEDNESDAY' BEGIN SELECT DATEADD(DAY,3,@DATE) AS DATE, 'IS THE NEAREST SATURDAY' END ELSE IF DATENAME(DW,@DATE) = 'THURSDAY' BEGIN SELECT DATEADD(DAY,2,@DATE) AS DATE, 'IS THE NEAREST SATURDAY' END ELSE IF DATENAME(DW,@DATE) = 'FRIDAY' BEGIN SELECT DATEADD(DAY,1,@DATE) AS DATE, 'IS THE NEAREST SATURDAY' END ELSE IF DATENAME(DW,@DATE) = 'SATURDAY' BEGIN SELECT CONVERT(NVARCHAR,@DATE) + ' IS SATURDAY' AS DATE_DOW END 

As we can see, the query is long and runs several IFs to find TRUE. Please do not write a request, just hints. I would like to work on the request myself.

Answer 1

Here is my hint, not responding as you requested:

Consider date manipulations using the current date (you can use GETDATE() or your date variable), DATEADD() and DATEDIFF() can be used to write in a couple of lines.

If you just want to simplify your method, you can use the CASE statement:

 DECLARE @date DATE = '2013-09-13' SELECT CASE WHEN DATENAME(DW,@DATE) = 'SUNDAY' THEN CAST(DATEADD(DAY,-3,@DATE) AS VARCHAR(12)) + ' IS THE NEAREST SATURDAY' WHEN DATENAME(DW,@DATE) = 'MONDAY' THEN CAST(DATEADD(DAY,-2,@DATE) AS VARCHAR(12)) + ' IS THE NEAREST SATURDAY' WHEN DATENAME(DW,@DATE) = 'TUESDAY' THEN CAST(DATEADD(DAY,-1,@DATE) AS VARCHAR(12)) + ' IS THE NEAREST SATURDAY' WHEN DATENAME(DW,@DATE) = 'WEDNESDAY' THEN CAST(DATEADD(DAY,1,@DATE) AS VARCHAR(12)) + ' IS THE NEAREST SATURDAY' WHEN DATENAME(DW,@DATE) = 'THURSDAY' THEN CAST(DATEADD(DAY,2,@DATE) AS VARCHAR(12)) + ' IS THE NEAREST SATURDAY' WHEN DATENAME(DW,@DATE) = 'FRIDAY' THEN CAST(DATEADD(DAY,3,@DATE) AS VARCHAR(12)) + ' IS THE NEAREST SATURDAY' ELSE CONVERT(NVARCHAR,@DATE) + ' IS SATURDAY' END 

To clarify the method that I hinted at, and Sparky, you must configure DATEFIRST to do this work, it works depending on what day will be on the first day of the week, Saturday is the 7th day of the week, like this:

 SET DATEFIRST 7 DECLARE @date DATE = '2013-09-21' SELECT DATEADD(day,7-DATEPART(weekday,@date),@date) 

Answer 2

Try the following:

 select dateAdd(dd,7-DATEPART(dw,getDate()),GETDATE()) 

datePart (dw, ...) returns the day of the week for the current date.

7 is a number , returns the number of days until Saturday

Add the result to the date to get the next Saturday ...

Similar logic if you need to go back to the previous Saturday

Sql Fiddle: http://www.sqlfiddle.com/#!3/61998/2

Answer 3

Well, not quite the answer, but an alternative

  DECLARE @DATE DATE, @SUN DATE, @MON DATE, @TUE DATE, @WED DATE, @THU DATE, @FRI DATE, @SAT DATE SET @DATE = '2013-09-14' SET @SUN = DATEADD(DAY,-1,@DATE) SET @MON = DATEADD(DAY,-1,@DATE) SET @TUE = DATEADD(DAY,-2,@DATE) SET @WED = DATEADD(DAY,-3,@DATE) SET @THU = DATEADD(DAY,2,@DATE) SET @FRI = DATEADD(DAY,1,@DATE) SET @SAT = DATEADD(DAY,0,@DATE) SELECT CASE WHEN DATENAME(DW,@DATE) = 'SUNDAY' THEN @SUN WHEN DATENAME(DW,@DATE) = 'MONDAY' THEN @MON WHEN DATENAME(DW,@DATE) = 'TUESDAY' THEN @TUE WHEN DATENAME(DW,@DATE) = 'WEDNESDAY' THEN @WED WHEN DATENAME(DW,@DATE) = 'THURSDAY' THEN @THU WHEN DATENAME(DW,@DATE) = 'FRIDAY' THEN @FRI ELSE @SAT END