Solved

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

Posted on 2016-07-26
2
132 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
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

856 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