I am looking for a way to return a short date value for a weekday in SQL Server.
for e.g. if the day is "Wednesday", then I want to return the date of the current week for a Wednesday. Assuming Monday is the first day of the week.
Hope this makes sense.
I'm sure there's a better solution, but this gives you your desired result:
Declare @DayOfWeek Varchar (10) = 'Wednesday' ;With Date (Date) As ( Select Convert(Date, DateAdd(Week, DateDiff(Week, 0, GetDate()), 0) - 1) Union All Select DateAdd(Day, 1, Date) From Date Where Date < DateAdd(Day, 6, DateAdd(Week, DateDiff(Week, 0, GetDate()), 0) - 1) ) Select Date From Date Where DateName(WeekDay, Date) = @DayOfWeek