CODEWITHSUNDEEP
sqlsql-servert-sql
user3482471
user3482471

Reputation: 227

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.

Upvotes: 0

Views: 68

Answers (3)

iceblade
iceblade

Reputation: 641

You could set the starting and ending date of the week and iterate trough those dates:

Declare @startDay   datetime,
        @endDay     datetime,
        @iterDate   datetime

Set @startDay = dateadd(day, datediff(day, 0, getdate()) / 7 * 7, 0) --Starting day of this week
Set @endDay = dateadd(day, 7, @startDay)

Set @iterDate = @startDay

Declare @tmpResults Table (Date datetime, DayName varchar(20))

While (@iterDate < @endDay)

Begin
    Insert into @tmpResults (Date, DayName)
    Values (@iterDate, datename(weekday, @iterDate))

    Set @iterDate = dateadd(day, 1, @iterDate)
End

select Date, DayName From @tmpResults

Results:

enter image description here

Upvotes: 0

Mansoor
Mansoor

Reputation: 4192

DECLARE @StartDT DATE = '2016-11-21'

;WITH CTE (_Date)
AS (
    SELECT @StartDT

    UNION ALL

    SELECT DATEADD(DAY, 1, _Date)
    FROM CTE
    WHERE _Date < DATEADD(DAY, 6, @StartDT)
    )
SELECT _Date
    ,DATENAME(W, _Date)
FROM CTE

Upvotes: 0

Rich Benner
Rich Benner

Reputation: 8113

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

Upvotes: 3

Related Questions