0

For any given date I need to determine what day of the week it is.

I already know how to get the day of the week with this DATENAME(dw,MyDate.Field)and the number of the day with this DATEPART(dw,MyDate.Field).

Once I've got the day of the week if it is before Wednesday I want to return that Wednesday's date.

If the day of the week is Wednesday or after then I want to return next Wednesdays date.

Monday is day 1 in my system.

neuhaus
  • 3,886
  • 1
  • 10
  • 27
Simon Farnworth
  • 15
  • 2

2 Answers2

1

Use DATEPART() to determine day of the week.

Use CASE() for the different cases.

Calculate the day delta. Hint: It's either 3-dw or 7+3-dw.

Use DATEADD() to get from the current day (returned by DATEPART) to Wednesday or Wednesday of next week.

neuhaus
  • 3,886
  • 1
  • 10
  • 27
  • This is more of a comment than an answer. Providing a working example would make this an answer. – Sean Lange Sep 05 '17 at 13:16
  • 1
    No, I'm not doing 100% of the work for him. If he can't figure it out from here, he ought to learn very basic algebra and SQL. The site documentation states: "Any answer that gets the asker going in the right direction is helpful" – neuhaus Sep 05 '17 at 13:17
  • Never suggested you do everything for them. But as it stands this isn't an answer, it is a comment with an accurate nudge in the right direction. – Sean Lange Sep 05 '17 at 13:18
  • i think all he is missing is `CASE`. – neuhaus Sep 05 '17 at 13:21
  • 1
    Thanks for the comment @neuhaus I've got it now! – Simon Farnworth Sep 05 '17 at 13:26
1

the easiest way, without any calculating:

set datefirst 1;
with dates as (
 select CAST('20170906' as datetime) d
 union all
 select DATEADD(day, 1, dates.d) 
 from dates
 where DATEADD(day, 1, dates.d) <= '20170930'
)
select dates.d, DATEADD(day, v.valueToAdd, dates.d) nextWed
from dates
    join (values(1,2),(2,1),(3,7),(4,6),(5,5),(6,4),(7,3))v(dayOfWeek,valueToAdd) on v.dayOfWeek = DATEPART(weekday, dates.d)
order by 1
avb
  • 1,743
  • 1
  • 13
  • 23