Pipe2Path Pipe2Path - 7 months ago 15
SQL Question

How to get the date this week from the day

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.

Thanks.

Answer

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

Result:

Date
2016-04-27