user3482471 user3482471 - 14 days ago 6
SQL Question

Calculating this weeks date range

I am looking to find out if there is a way in which I can calculate this weeks date range.

For instance

Monday - 2016-11-21
Tuesday - 2016-11-22
Wednesday - 2016-11-23


and so on an so forth.

If some one can help me out with the syntax and calculation that would be great.

Answer

If you're happy with them in their own columns then you can do something like this;

SELECT
DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 0) Last_Monday
,DATEADD(WEEK, DATEDIFF(WEEK, 2, GETDATE()) - 1, 1) Last_Tuesday
,DATEADD(WEEK, DATEDIFF(WEEK, 2, GETDATE()) - 1, 2) Last_Wednesday
,DATEADD(WEEK, DATEDIFF(WEEK, 2, GETDATE()) - 1, 3) Last_Thursday

Result;

Last_Monday             Last_Tuesday            Last_Wednesday          Last_Thursday
2016-11-14 00:00:00.000 2016-11-15 00:00:00.000 2016-11-16 00:00:00.000 2016-11-17 00:00:00.000

If you need it in rows then just union the result;

SELECT DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 0) Date_Field, 'Last_Monday' Day_Name
UNION
SELECT DATEADD(WEEK, DATEDIFF(WEEK, 2, GETDATE()) - 1, 1), 'Last_Tuesday'
UNION
SELECT DATEADD(WEEK, DATEDIFF(WEEK, 2, GETDATE()) - 1, 2), 'Last_Wednesday'
UNION 
SELECT DATEADD(WEEK, DATEDIFF(WEEK, 2, GETDATE()) - 1, 3), 'Last_Thursday'

Result;

Date_Field                  Day_Name
2016-11-14 00:00:00.000     Last_Monday
2016-11-15 00:00:00.000     Last_Tuesday
2016-11-16 00:00:00.000     Last_Wednesday
2016-11-17 00:00:00.000     Last_Thursday

Take a read here for a good explanation of how this works;

DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) Can someone explain me this