44

I need the correct syntax to give me :

  1. Previous week's Monday's date based on the current date/time using GETDATE()
  2. Previous week's Sunday's date based on the current date/time using GETDATE()

So, based on today's date (14/09/2012) I would want the following:

  1. Previous Monday's date = 03/09/2012
  2. Previous Sunday's date = 09/09/2012
Lee Taylor
  • 7,761
  • 16
  • 33
  • 49
JsonStatham
  • 9,770
  • 27
  • 100
  • 181
  • 1
    Wasn't last monday on 10th? Are you looking for previous week's monday? – Nikola Markovinović Sep 14 '12 at 10:02
  • why is your last monday not `10/09/2012`? – John Woo Sep 14 '12 at 10:02
  • Sorry, i was classing that as this monday (in the current week) so yes, previous weeks monday would be a better turn of phrase – JsonStatham Sep 14 '12 at 10:03
  • [Here is related answer by Aaron Bertrand](http://stackoverflow.com/questions/7168874/get-first-day-of-week-in-sql-server). You just need to decide which function you want and to deduct 1 or 7 from result to get sunday and monday of previous week. – Nikola Markovinović Sep 14 '12 at 10:09
  • Does this seem correct/bug proof: SELECT dateadd(week, datediff(week, 0, getdate()), 0)-7as PreviousWeeksMonday; SELECT dateadd(week, datediff(week, 0, getdate()), 0)-1as PreviousWeeksSunday; – JsonStatham Sep 14 '12 at 10:21
  • 1
    If you want to notify someone you should put somewhere in your comment @ followed by one of commentators name. Only original posters get notifications about any comment attached to the post. That aside, your snippet will work except for Sunday. Datediff for some reason hardcodes Sunday as start of week, so you get difference of one week on Sundays thus arriving at next monday as start of the week - and ironically sunday becomes LastWeeksSunday. To avoid the problem use `getdate() - 1` - if Sunday, it moves to previous week, if Monday or any other day it stays in current week. – Nikola Markovinović Sep 14 '12 at 12:27

8 Answers8

67

Easy:

--start of last week
SELECT DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 0)

--end of last week
SELECT DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 6)

EDIT:

The below will handle the Sunday date issue.

DECLARE @input varchar(10)
--SET @input = '9/9/2012' -- simulates a Sunday
SET @input = GETDATE()

--start of last week
SELECT DATEADD(wk, DATEDIFF(wk, 6, 
CASE DATEPART(dw,@input)
WHEN 1 THEN DATEADD(d,-1,@input)
ELSE @input
END
), 0)

--end of last week
SELECT DATEADD(wk, DATEDIFF(wk, 6, 
CASE DATEPART(dw,@input)
WHEN 1 THEN DATEADD(d,-1,@input)
ELSE @input
END
), 6)
general exception
  • 4,202
  • 9
  • 54
  • 82
  • This falls to the same bug as @Nikola Markovinović mentions above though. – general exception Sep 14 '12 at 12:47
  • Added code as per @Nikola Markovinović suggested fix in the above comment. – general exception Sep 14 '12 at 15:29
  • O was thinking that datediff sees Sunday as start of the week, and we want to start with monday, test for given date would be performed by testing previous day. This way Sunday would be moved to previous week while other days would remain in their week – Nikola Markovinović Sep 14 '12 at 16:12
  • For those wondering the @input is hardcoded to simulate a Sunday. Replace this with GETDATE() to use the current date as per the OP's question. – general exception Sep 17 '12 at 08:10
  • 2
    Doesn't DATEPART return a different value depending on @@DATEFIRST? That would mean it is not necessarily equal to 1. – Clement May 21 '19 at 07:38
  • The literal integers `6` and `0` inside the `DATEDIFF` and `DATEADD` call-sites are implicitly converted to the `date` values `1900-01-07` and `1900-01-01` respectively - this won't work for `datetime2` and `datetimeoffset` types which both disallow implicit conversion from single integers. – Dai Sep 22 '22 at 22:51
  • Sorry, is the start of a week Sunday? Or is Sunday the end of a week – Patrick Stetz Oct 05 '22 at 21:57
18

Instead of using a case option, you could also do this to get the current week's Sunday:

SELECT DATEADD(dd, DATEPART(DW,GETDATE())*-1+1, GETDATE())

To get the previous week's Sunday, subtract 7 more days:

SELECT DATEADD(dd, DATEPART(DW,GETDATE())*-1-6, GETDATE())
jwarwani
  • 201
  • 3
  • 6
  • 1
    if @@datefirst = 7 (default) the first one gives me previous Sunday (unless you count the week starting at Sunday... then it is ok, but then the whole case option is also out of question. – robotik Jul 25 '16 at 12:12
  • @robotik, right. I had the same thought. When I ran the example on my DB, I was execting different dates to show up. Main difference is, we have the week setup as Monday = 2, but we run a business calendar where Monday = 1. So, as I implement the code on my query, I need to keep the difference in mind. – Aaron C Mar 12 '18 at 15:29
11

Previous Monday:

SELECT DATEADD(DD,-(DATEPART(WEEKDAY, GETDATE())+5)%7, GETDATE())

Previous Sunday:

SELECT DATEADD(DD,-(DATEPART(WEEKDAY, GETDATE())+6)%7, GETDATE())
Lee Taylor
  • 7,761
  • 16
  • 33
  • 49
  • You need to subtract -1 from both occurrences of GetDate() if you want the previous Monday/Sunday.. you may end up getting today's date if it is Monday/Sunday otherwise.. – PepiX Apr 11 '22 at 14:44
  • 1
    Appreciate this. Not sure on the comment above on -1, it works as given. The modulo operator does nothing if it is a Monday and allows subtraction on all other days, which is how it should work. – giraffehere May 17 '22 at 02:36
  • It would probably be a bit clearer to add an extra parentheses like so: `-((DATEPART(WEEKDAY, GETDATE())+5)%7)`, just to emphasize the negation comes after the operator, despite orders of operation covering it. – giraffehere May 17 '22 at 02:42
5

I think this is much cleaner solution:

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
Rafael
  • 7,002
  • 5
  • 43
  • 52
  • 1
    This is the solution that works with the least manipulation as far as i'm aware (although the lower limit shows the monday of the previous week, rather than the monday of the current week which the OP requests). Other solutions suffer from the 'Sunday problem' – Gallus May 23 '17 at 16:25
5

Even better, I think, this works for any date, any week day, with any DateFirst parameter (set the first day of the week, generally 1-Monday in France, default is 7-Sunday).

create function [dbo].[previousWeekDayDate](@anyDate date, @anyWeekDay int)
returns Date
as
begin
    return DATEADD(dd, ((DATEPART(dw,@anyDate) + @@DateFirst - @anyWeekDay + 13) % 7) * -1, @anyDate)
end

works for SQL 2008, create the function and use:

SELECT dbo.previousWeekDayDate(GetDate(),1) --for Monday
SELECT dbo.previousWeekDayDate(GetDate(),7) --for Sunday
Pascal
  • 51
  • 1
  • 1
  • why +13 though? +6 or -1 is cleaner and shorter and works just as well. anyway it's great that you incorporated @@datefirst into the equasion – robotik Jul 25 '16 at 05:58
3

It should be noted that the issue with Sundays appears to no longer be present at least as of MSSQL 2012. Both the simple solution

SELECT DATEADD(wk, DATEDIFF(wk, 6, @input), 0)

and the complex one

SELECT DATEADD(wk, DATEDIFF(wk, 6, 
CASE DATEPART(dw,@input)
WHEN 1 THEN DATEADD(d,-1,@input)
ELSE @input
END
), 0)

return the same for any date that I've tried, including Sundays.

RedAero
  • 49
  • 3
0
SELECT DATEADD(DAY, -DATEDIFF(DAY, 6, GETDATE()) % 7, GETDATE())

return the last Sunday.

DATEDIFF(DAY, 1, GETDATE()) : returnd the count of days from first day in system. The first date of the system is Tuesday,

module (%) 7 Returns the remainder of days that are not a whole week.

Moshe Klien
  • 19
  • 3
-1

PREVIOUS WEEK DAY-

SELECT NEXT_DAY(SYSDATE-8,'SUNDAY') FROM DUAL;

THANKS

Anurag Singh
  • 1
  • 3
    Are you sure that you are answering the question at the top of this page? It does not seem to ask for anything like this. – Yunnosch Apr 28 '22 at 07:09
  • 2
    Welcome to Stack Overflow! While this code may solve the question, [including an explanation](//meta.stackexchange.com/q/114762) of how and why this solves the problem would really help to improve the quality of your post, and probably result in more up-votes. Remember that you are answering the question for readers in the future, not just the person asking now. Please [edit] your answer to add explanations and give an indication of what limitations and assumptions apply. – Yunnosch Apr 28 '22 at 07:09