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 Comments1 Solution768 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: http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_28182893.html

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())))

(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?
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 14 Comments.
Join the Community
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 1 Answer 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