How to get last week date range based on current date in sql?

Question

I have this code in crystal reports that gives me last week date range based on the current date.

First day of the week:

If DayOfWeek(currentdate) = 2 Then
currentdate
Else If DayOfWeek(currentdate) = 3 Then
dateadd ("d",-1,currentdate)
Else If DayOfWeek(currentdate) = 4 Then
dateadd ("d",-2,currentdate)
Else If DayOfWeek(currentdate) = 5 Then
dateadd ("d",-3,currentdate)
Else If DayOfWeek(currentdate) = 6 Then
dateadd ("d",-4,currentdate)
Else If DayOfWeek(currentdate) = 7 Then
dateadd ("d",-5,currentdate)
Else If DayOfWeek(currentdate) = 1 Then
dateadd ("d",-6,currentdate)

Last day of week:

If DayOfWeek(currentdate) = 2 Then
dateadd ("d",+6,currentdate)
Else If DayOfWeek(currentdate) = 3 Then
dateadd ("d",+5,currentdate)
Else If DayOfWeek(currentdate) = 4 Then
dateadd ("d",+4,currentdate)
Else If DayOfWeek(currentdate) = 5 Then
dateadd ("d",+3,currentdate)
Else If DayOfWeek(currentdate) = 6 Then
dateadd ("d",+2,currentdate)
Else If DayOfWeek(currentdate) = 7 Then
dateadd ("d",+1,currentdate)
Else If DayOfWeek(currentdate) = 1 then currentdate

How can I do the same in SQL using 2 variables to storage Monday(startdate) and Sunday(enddate)?

I found this select datepart(dw,getdate()) --6 in this site, but I do not know how to use it.

Bill Stidham · Accepted Answer · 2014-03-31T20:19:13.760

I generated some spaced out dates in the parms CTE then SELECT the CurrentDate from parms, the Sunday of the week prior to CurrentDate and the Saturday of the week prior to CurrentDate. I'm assuming that you want the dtate range to be Sunday - Saturday.

Sunday - Saturday Ranges

;WITH parms (CurrentDate) AS (
    SELECT DATEADD(dd, -14, CURRENT_TIMESTAMP) UNION 
    SELECT DATEADD(dd, -6, CURRENT_TIMESTAMP) UNION
    SELECT DATEADD(dd, 2, CURRENT_TIMESTAMP) UNION
    SELECT DATEADD(dd, 8, CURRENT_TIMESTAMP) UNION
    SELECT DATEADD(dd, 15, CURRENT_TIMESTAMP) UNION
    SELECT DATEADD(dd, 20, CURRENT_TIMESTAMP)
)

SELECT CurrentDate
     , LastWeekSunday   = DATEADD(dd, -1, DATEADD(ww, DATEDIFF(ww, 0, CurrentDate) - 1, 0))
     , LastWeekSaturday = DATEADD(dd,  5, DATEADD(ww, DATEDIFF(ww, 0, CurrentDate) - 1, 0))
FROM parms

Monday to Sunday Ranges

;WITH parms (CurrentDate) AS (
    SELECT DATEADD(dd, -14, CURRENT_TIMESTAMP) UNION 
    SELECT DATEADD(dd, -6, CURRENT_TIMESTAMP) UNION
    SELECT DATEADD(dd, 2, CURRENT_TIMESTAMP) UNION
    SELECT DATEADD(dd, 8, CURRENT_TIMESTAMP) UNION
    SELECT DATEADD(dd, 15, CURRENT_TIMESTAMP) UNION
    SELECT DATEADD(dd, 20, CURRENT_TIMESTAMP)
)

SELECT CurrentDate
     , LastWeekMonday   = DATEADD(dd,  0, DATEADD(ww, DATEDIFF(ww, 0, DATEADD(dd, -1, CurrentDate)) - 1, 0))
     , LastWeekSunday   = DATEADD(dd,  6, DATEADD(ww, DATEDIFF(ww, 0, DATEADD(dd, -1, CurrentDate)) - 1, 0))
FROM parms

If you just want the prior week's Monday to the prior week's Sunday from today rather than from a column of dates you can use this

SELECT CURRENT_TIMESTAMP
     , LastWeekSunday   = DATEADD(dd,  0, DATEADD(ww, DATEDIFF(ww, 0, DATEADD(dd, -1, CURRENT_TIMESTAMP)) - 1, 0))
     , LastWeekSaturday = DATEADD(dd,  6, DATEADD(ww, DATEDIFF(ww, 0, DATEADD(dd, -1, CURRENT_TIMESTAMP)) - 1, 0))

@cojimarmiami, the numbers are a little different for Monday to Sunday ranges. I added the code to use for Monday - Sunday but left the Sunday - Saturday code and labeled both. — Bill Stidham, Mar 31 '14 at 19:33
could you please explain me the logic behind that statement because when I run it I get 6 different LastWeekMonday and the same for LastWeekSunday — asfsdf, Mar 31 '14 at 19:41
The `parms` CTE is just there to generate a couple of dates (six rows actually). That's why you are getting six rows of results. You would just use the `SELECT` statement at the end and replace "parms" with your actual table name. — Bill Stidham, Mar 31 '14 at 19:45
@cojimarmiami, My code also assumes you are selecting from a date column in a table called CurrentDate. I added a third section to get the prior week's date range using just today's date. — Bill Stidham, Mar 31 '14 at 20:20

Rafael · Answer 2 · 2018-08-24T19:47:51.640

This solution is tested and works. I am getting the previous week's Monday and Sunday as upper and lower bounds.

SELECT
    -- 17530101 or 1753-01-01 is the minimum date in SQL Server
    DATEADD(dd, ((DATEDIFF(dd, '17530101', GETDATE()) / 7) * 7) - 7, '17530101') AS [LowerLimit], -- Last Week's Monday
    DATEADD(dd, ((DATEDIFF(dd, '17530101', GETDATE()) / 7) * 7) - 1, '17530101') AS [UpperLimit] -- Last Week's Sunday.

Which can be used like this in a real world query:

SELECT
    *
FROM
    SomeTable
WHERE
    SomeTable.[Date] >= DATEADD(dd, ((DATEDIFF(dd, '17530101', GETDATE()) / 7) * 7) - 7, '17530101') AND
    SomeTable.[Date] <= DATEADD(dd, ((DATEDIFF(dd, '17530101', GETDATE()) / 7) * 7) - 1, '17530101')

Here are some tests:

1. Leap Year

Current Date: 2016-02-29 00:00:00.000

Results:

LowerLimit                 UpperLimit
2016-02-22 00:00:00.000    2016-02-28 00:00:00.000

2. Last Week was in different year

Current Date: 2016-01-06 00:00:00.000

LowerLimit                 UpperLimit
2015-12-28 00:00:00.000    2016-01-03 00:00:00.000

3. Lower limit in previous month and upper limit in current month

Current Date: 2016-05-04 00:00:00.000

LowerLimit                 UpperLimit
2016-04-25 00:00:00.000    2016-05-01 00:00:00.000

4. Current Date is Sunday

Current Date: 2016-05-08 00:00:00.000

LowerLimit                 UpperLimit
2016-04-25 00:00:00.000    2016-05-01 00:00:00.000

score 3 · Answer 3 · answered Sep 22 '16 at 13:01

Bill's code worked well, but I needed to make a slight edit if I wanted to get last Sunday to Saturday based on today's date.

select CURRENT_TIMESTAMP, 
LastWeekSunday   = DATEADD(dd,  -1, DATEADD(ww, DATEDIFF(ww, 0, DATEADD(dd, -1, CURRENT_TIMESTAMP)) - (case datename(weekday,CURRENT_TIMESTAMP) when 'Sunday' then 0 else 1 end), 0)), 
LastWeekSaturday = DATEADD(dd,  5, DATEADD(ww, DATEDIFF(ww, 0, DATEADD(dd, -1, CURRENT_TIMESTAMP)) - (case datename(weekday,CURRENT_TIMESTAMP) when 'Sunday' then 0 else 1 end), 0))

Rgds - Matt

dean · Answer 4 · 2014-03-31T19:21:54.707

2

For everything date and date interval related, I'd almost always recommend using a calendar table. A table with columns representing date, week, month, year, day of week, hiliday, weekand, etc, prepopulated as needed and indexed on every column. It's only 365 rows per year.

select min(date), max(date)
from calendar
where week = datepart(week, getdate() - 7)
and year = datepart(year, getdate() - 7)

edited Mar 31 '14 at 19:21

answered Mar 31 '14 at 19:12

dean

9,960
2
25
26

what happen is that code will be included in a SP that will be used in crystal reports in an auto delivery report. – asfsdf Mar 31 '14 at 19:14

score 0 · Answer 5 · answered Feb 11 '22 at 04:33

A shortened version concise syntax

last week, in my vision is the prev adjacent week, so if Im doing report during the week days, I want to receive dates of previous adjacent week Monday to Sunday.

prev Monday is DATE_ADD(CURDATE(), INTERVAL -(5 + dayofweek(CURDATE())) DAY)
prev Sunday is DATE_ADD(CURDATE(), INTERVAL -(dayofweek(CURDATE()) - 1) DAY)
then to take the report from the table need just to integrate those two in SELECT statement

select * from XXX where DateCol between DATE_ADD(CURDATE(), INTERVAL -(5 + dayofweek(CURDATE())) DAY) and DATE_ADD(CURDATE(), INTERVAL -(dayofweek(CURDATE()) - 1) DAY) order by DateCol

score 0 · Answer 6 · answered Dec 08 '22 at 21:54

I'm late to this party, but wanted to add a simpler way that works for me in SQL Server (when the other examples did not).

select * from table

-- this is for the previous week:

where (datefield >= DATEADD(dd, -1, DATEADD(ww, DATEDIFF(ww, 0, GETDATE()) - 1, 0)) and datefield < DATEADD(dd,  6, DATEADD(ww, DATEDIFF(ww, 0, GETDATE()) - 1, 0)))

-- alternatively, this is for a rolling 7 days:

and DATEDIFF(day, datefield, GETDATE()) between 0 and 7

How to get last week date range based on current date in sql?

6 Answers6