asked on

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

Sirees

Try this

CONVERT(varchar(10), DATEADD(day, 1 - DATEPART(weekday, table.date) + 5, table.date), 101)

lyptus

ASKER

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.

lyptus

ASKER

Edit... it is assuming Thursday is the end of the week, not Saturday.

lyptus

ASKER

Sorry, scratch that edit. I was looking at the wrong calendar

ASKER CERTIFIED SOLUTION

MikeWalsh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

lyptus

ASKER

Thank you!