DatePart Question

I have a chart that displays the number of downloads by product by week by year. I am using sql to group by DATEPART(WK, DownloadDate) and DATEPART(YY, DownloadDate). This however shows the week number (ex: this week is 49) on the x axis. I would like to show the tick marks as the Sunday and Month of that week. Example: 12/3. The year is shown under these so that part is fine. Any ideas how I might accomplish this?

I had been running into this same issue and was not able to find a solution, however I was able to work something out that does the job for me. Try the code below. Change "1" to use a day other than Sunday for the first day of the week.

Code Snippet

=Format(DateAdd("d", -(Weekday(Now()))+1, DateAdd("ww", -(DatePart("ww", Now())-DatePart("ww",DownloadDate)), Now())), "M/d/yy")

There are probably better and easier ways to do it, but it works for me. Hope this helps (even though it's a little late)!

Scott