user3482471 - 1 year ago 80
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.

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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download