• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 774
  • Last Modified:

Calculating Week Start Date & Week End Date in SQL 2012 "Week begins on a Sunday"

I'm currently calculating "Month to Date" & "Year to Date" sales however I need to calculate "Week to Date" where my week begins on a Sunday.

Here is how I'm calculating the MTD & YTD

SELECT  Period_End_Date AS MthEndDate ,
        DATEADD(mm, DATEDIFF(mm, 0, Period_End_Date), 0) AS MthStartDate ,
        DATEADD(yy, DATEDIFF(yy, 0, Period_End_Date), 0) AS YearStartDate ,
        DATEADD(yy, DATEDIFF(yy, 0, Period_End_Date) + 1, -1) AS YearEndDate
FROM    [KLL AR Periods - Vertical]
WHERE   ( AR_Period = '201607' );

Open in new window

What do I need to add to this to get WkStartDate & WkEndDate?
2 Solutions
Brian PringleSystems Analyst II, SCM, ERPCommented:
You can use 'weekday' with the datepart function to get the date of the first and last days of the week.

Russ SuterCommented:
The following will always give you the Sunday of the week containing the date specified
SELECT DATEADD(DAY, 1 - DATEPART(dw, @DateValue), @DateValue)

Open in new window

Then you can just do this to get the following Saturday
SELECT DATEADD(DAY, 6, (DATEADD(DAY, 1 - DATEPART(dw, @DateValue), @DateValue)))

Open in new window

Then just select all your data between those two dates.

Be careful when using DATE objects against a DATETIME field. Saying WHERE [SomeDate] BETWEEN '7/24/16' AND '7/30/16' for example will give you everything in the range of:
2016-07-24 00:00:00.000
2016-07-30 00:00:00.000
Which means entries on 7/30/16 after midnight would be omitted. To get around this you have two options:
1. Cast the DATETIME field to a DATE (this sometimes has performance issues)
2. Alter the above so the resulting calculation ends up as 2016-07-30 23:59:59.999 which would be like this:

Open in new window

Now, there are a couple of quirks here. SQL Server's DATETIME object doesn't exactly have the precision required to render to the single millisecond even though it does have the scale to do so. Adding -1 millisecond actually will do nothing to the final output. Adding -2 milliseconds will leave you with 2016-07-30 23:59:59.997. So what you have is a 3 millisecond gap that you can't really pin down. Of course the chances of one of your entries being exactly inside that 3 millisecond gap around midnight are extremely slim.

If you need the performance then you can pre-calculate these date values and use them in your query. You gain performance at the expense of those 3 milliseconds. If you absolutely must have the millisecond accuracy then you'll need to cast the column as a DATE. Or if you don't care at all or your column is already a DATE then you're good to go.
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.

Join & Write a Comment

Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

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