troubleshooting Question

MS SQL - Get dates for week where DatePart(week, @date)

Avatar of forsters
forstersFlag for United Kingdom of Great Britain and Northern Ireland asked on
Microsoft SQL ServerSQL
14 Comments4 Solutions768 ViewsLast Modified:
Hi Experts,

This follows on from a previous questionin which I was given some great answers but I now want to tweak the syntax slightly to schieve different things, hoping someone can help.

This is the earlier question:

So using the query that gets weekdays for the current week:

WITH days AS (SELECT        - 7 AS d
                                 UNION ALL
                                 SELECT        d + 1 AS Expr1
                                 FROM            days AS days_2
                                 WHERE        (d < 7))
    SELECT        TOP (5) DATEADD(day, d, GETDATE()) AS date, DATENAME(weekday, DATEADD(day, d, GETDATE())) AS Days
     FROM            days AS days_1
     WHERE        (DATEPART(week, GETDATE()) = DATEPART(week, DATEADD(day, d, GETDATE())))

Open in new window

(note I have modified the original code provided by the expert in my previous question by removing the conversion and selecting top 5 to omit the weekend days from my results)

What I would like to do now is pass in a parameter rather than use GetDate(), so that the query become get me the weekdays where the date I am passing in i.e. the parameter @date is in that week, as before I want dates back and day names not the day's number.

I hope someone can help, many thanks in advance.

As an afterthought I also want to be able to increment through the weeks - is that possible?
Ioannis Paraskevopoulos

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 4 Answers and 14 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 4 Answers and 14 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros