Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 657
  • 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?
0
mburk1968
Asked:
mburk1968
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.

https://msdn.microsoft.com/en-us/library/ms174420.aspx
0
 
Russ SuterCommented:
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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

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