Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 750
  • Last Modified:

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
0
lyptus
Asked:
lyptus
  • 4
1 Solution
 
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
 
lyptusAuthor Commented:
Edit... it is assuming Thursday is the end of the week, not Saturday.
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
lyptusAuthor Commented:
Sorry, scratch that edit. I was looking at the wrong calendar
0
 
MikeWalshCommented:
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
 
lyptusAuthor Commented:
Thank you!
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now