Pipe2Path Pipe2Path - 1 year ago 79
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.


Answer Source

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


Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download