Help w/ DatePart and DateAdd to get weekending

I have a select statment that will tell me how many records were recorded each week. The result shows me the date of the end of the week and how many records. Ex.
weekending     num
4/15/06          5
4/22/06          6

SELECT     CONVERT(varchar(10), DATEADD(day, 1 - DATEPART(weekday, table.date) + 6, table.date), 101) AS Weekending, COUNT(table.date) AS Num
FROM         table
GROUP BY CONVERT(varchar(10), DATEADD(day, 1 - DATEPART(weekday, table.date) + 6, table.date), 101)

This statement assumes saturday is the end of the week. I need to modify this statment so I can change the end of the week to a different day. So if I want the end of the week to be friday the result set would show....

weekending     num
4/14/04          4
4/21/06          7
lyptusAsked:
Who is Participating?
 
MikeWalshConnect With a Mentor Commented:
I think that the SET DATEFIRST keyword will help you out here.. This command tells SQL for your session what date is the first day of the week (look it up in SQL Books Online)..

So in your case if Friday is the last day of the week, then you want Saturday to be the first day of the week

SET DATEFIRST 6 -- this will make Saturday the first day of the week, and Friday the last day of the week.

So now you should be able to use this knowledge with your script and add the appropriate number to find the next week..

0
 
SireesCommented:
Try this

 CONVERT(varchar(10), DATEADD(day, 1 - DATEPART(weekday, table.date) + 5, table.date), 101)
0
 
lyptusAuthor Commented:
Sirees,
Thanks for the reply. The counts for the weeks don't come out right if I change 6 to 5. It thinks that a record that happened on 4/15/06 happened in weekending 4/14/06 instead of weekending 4/21/06 when it happened.
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
lyptusAuthor Commented:
Edit... it is assuming Thursday is the end of the week, not Saturday.
0
 
lyptusAuthor Commented:
Sorry, scratch that edit. I was looking at the wrong calendar
0
 
lyptusAuthor Commented:
Thank you!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.