Solved

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

Posted on 2016-07-26
2
211 Views
Last Modified: 2016-07-26
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?
0
Comment
Question by:mburk1968
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 16

Assisted Solution

by:Brian Pringle
Brian Pringle earned 250 total points
ID: 41729550
You can use 'weekday' with the datepart function to get the date of the first and last days of the week.

https://msdn.microsoft.com/en-us/library/ms174420.aspx
0
 
LVL 20

Accepted Solution

by:
Russ Suter earned 250 total points
ID: 41729555
The following will always give you the Sunday of the week containing the date specified
DECLARE @DateValue DATE = GETDATE()
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
to
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:
SELECT DATEADD(MILLISECOND, -2, (DATEADD(DAY, 7, (DATEADD(DAY, 1 - DATEPART(dw, @DateValue), @DateValue)))))

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

Featured Post

Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
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.

710 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