Solved

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

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

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.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

920 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

15 Experts available now in Live!

Get 1:1 Help Now