Help w/ DatePart and DateAdd to get weekending

Posted on 2006-06-05
Last Modified: 2007-12-19
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, + 6,, 101) AS Weekending, COUNT( AS Num
FROM         table
GROUP BY CONVERT(varchar(10), DATEADD(day, 1 - DATEPART(weekday, + 6,, 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
Question by:lyptus
    LVL 20

    Expert Comment

    Try this

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

    Author Comment

    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.

    Author Comment

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

    Author Comment

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

    Accepted Solution

    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..


    Author Comment

    Thank you!

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Join & Write a Comment

    I recently came across an interesting Question In EE ( and was puzzled about how to achieve that using SSIS out of the box tasks, which was i…
    For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

    730 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now