SQL Date Formulas

Question

I need a date formula in Oracle SQL or T-SQL that will return a date of the previous week (eg Last Monday's date).

I have reports with parameters that are run each week usually with parameter dates mon-friday or sunday-saturday of the previous week. I'd like to not have to type in the dates when i run the reports each week.

The data is in Oracle and I am using SQL Server 2005 Reporting Services (SSRS) for the reports.

Tomalak · Answer 1 · 2008-10-27T16:54:07.533

2

T-SQL:

SELECT 
  DateColumn,
  DateColumn - CASE DATEPART(dw, DateColumn) 
                WHEN 1 THEN 6
                ELSE DATEPART(dw, DateColumn) - 2
              END MondayOfDateColumn
FROM 
  TheTable

Do you need the time part to be "00:00:00", too?

If so, add this expression to the calculation:

DATEADD(dd, 0, DATEDIFF(dd, 0, DateColumn)) - CASE DATEPART(dw, /* etc. etc. */

edited Oct 27 '08 at 16:54

answered Oct 27 '08 at 15:47

Tomalak

332,285
67
532
628

The formula accounts for that. – Tomalak Oct 27 '08 at 16:55
What does your calculation return for a Sunday date, such as '10/26/2008'? – DOK Oct 27 '08 at 19:04

score 2 · Answer 2 · edited Oct 27 '08 at 18:53

Here is an Oracle solution for Monday.

select sysdate - 5 - to_number(to_char(sysdate,'D')) from dual

Here are examples that retrieve any particular day from the previous week.

SELECT sysdate - 6 - to_number(to_char(sysdate,'D')) LastSunday FROM dual;
SELECT sysdate - 5 - to_number(to_char(sysdate,'D')) LastMonday FROM dual;
SELECT sysdate - 4 - to_number(to_char(sysdate,'D')) LastTuesday FROM dual;
SELECT sysdate - 3 - to_number(to_char(sysdate,'D')) LastWednesday FROM dual;
SELECT sysdate - 2 - to_number(to_char(sysdate,'D')) LastThursday FROM dual;
SELECT sysdate - 1 - to_number(to_char(sysdate,'D')) LastFriday FROM dual;
SELECT sysdate - 0 - to_number(to_char(sysdate,'D')) LastSaturday FROM dual;

If you need the time part to be 00:00:00 wrap the statment in TRUNC(...).

Anybody got the SQL Server equivalent? – DOK Oct 27 '08 at 19:28 — DOK, Oct 27 '08 at 19:28

score 1 · Answer 3 · edited May 23 '17 at 10:33

1

Check out the list of date functions in this post. You want this one.

SELECT (DATEADD(wk,DATEDIFF(wk,0,GETDATE()) -1 ,0))

They are almost always math and not string oriented so they will work faster than casing or casted operations

edited May 23 '17 at 10:33

Community

1
1

answered Oct 27 '08 at 15:47

StingyJack

19,041
10
63
122

This is seems to be off by one week. – Tomalak Oct 27 '08 at 16:02
Its the previous monday. If today is tuesday then its going to get yesterday – StingyJack Oct 28 '08 at 13:49

Amy B · Answer 4 · 2008-10-27T16:36:04.370

Here's my solution, tested against 8 days.

SET DateFirst 7

DECLARE @Today datetime

SET @Today = '2008-10-22'
SELECT DateAdd(wk, DateDiff(wk, 0, DateAdd(dd, -1, @Today)) - 1, 0) as PreviousMonday, @Today as Today
SET @Today = '2008-10-23'
SELECT DateAdd(wk, DateDiff(wk, 0, DateAdd(dd, -1, @Today)) - 1, 0) as PreviousMonday, @Today as Today
SET @Today = '2008-10-24'
SELECT DateAdd(wk, DateDiff(wk, 0, DateAdd(dd, -1, @Today)) - 1, 0) as PreviousMonday, @Today as Today
SET @Today = '2008-10-25'
SELECT DateAdd(wk, DateDiff(wk, 0, DateAdd(dd, -1, @Today)) - 1, 0) as PreviousMonday, @Today as Today

SET @Today = '2008-10-26'
SELECT DateAdd(wk, DateDiff(wk, 0, DateAdd(dd, -1, @Today)) - 1, 0) as PreviousMonday, @Today as Today
SET @Today = '2008-10-27'
SELECT DateAdd(wk, DateDiff(wk, 0, DateAdd(dd, -1, @Today)) - 1, 0) as PreviousMonday, @Today as Today
SET @Today = '2008-10-28'
SELECT DateAdd(wk, DateDiff(wk, 0, DateAdd(dd, -1, @Today)) - 1, 0) as PreviousMonday, @Today as Today
SET @Today = '2008-10-29'
SELECT DateAdd(wk, DateDiff(wk, 0, DateAdd(dd, -1, @Today)) - 1, 0) as PreviousMonday, @Today as Today

Here's the trouble with Sunday:

SELECT
  DateDiff(wk, 0, '2008-10-25') as SatWeek, --5677
  DateDiff(wk, 0, '2008-10-26') as SunWeek, --5688
  DateDiff(wk, 0, '2008-10-27') as MonWeek  --5688

SELECT
  DatePart(dw, '2008-10-25') as SatPart,  --7
  DatePart(dw, '2008-10-26') as SunPart,  --1
  DatePart(dw, '2008-10-27') as MonPart,  --2
  convert(datetime,'2008-10-25') - (DatePart(dw, '2008-10-25') - 2)  as SatMonday,
  --'2008-10-20'
  convert(datetime,'2008-10-26') - (-1)  as SunMonday,
  --'2008-10-27'
  convert(datetime,'2008-10-27') - (DatePart(dw, '2008-10-27') - 2)  as MonMonday
  --'2008-10-27'

Many of these solutions Provide the same answer for Sunday and Monday in the same week. The old Monday should not be resigned until another Monday has occurred.

Noah Yetter · Answer 5 · 2009-02-15T18:22:08.293

1

(Oracle)

trunc(sysdate,'IW') --gives this week's monday

trunc(sysdate,'IW')-7 --gives last week's monday

This assumes you consider monday to be the first day of the week, which is what 'IW' (ISO Week) signifies. If you consider sunday to be the first day of the week...

trunc(sysdate,'W')+1 --gives this week's monday, on sunday this will be in the future

trunc(sysdate,'W')+1-7 --gives last week's monday

edited Feb 15 '09 at 18:22

answered Oct 27 '08 at 18:40

Noah Yetter

396
2
9

On my system Sunday the 26th gives the previous Monday as the 13th rather than the 20th as follows: SELECT trunc(to_date('10/26/2008','MM/DD/YYYY'),'IW')-7 from dual; – Leigh Riffel Oct 27 '08 at 21:28
trunc(sysdate+1,'IW')-7 seems to fix the Sunday problem. – Leigh Riffel Oct 27 '08 at 21:36
Noah, perhaps you could test Leigh's version and amend yours accordingly, if it seems to work for all days of the week. I think the questioner would prefer one SQL statement that handles all possible dates. It might need a case/switch to do that. – DOK Oct 27 '08 at 21:46

JosephStyons · Answer 6 · 2008-10-27T18:53:27.277

In Oracle:

Edit: Made it a bit more concise

Edit: Leigh Riffel has posted a much better solution than mine.

select
  case when 2 = to_char(sysdate-1,'D') then sysdate - 1
       when 2 = to_char(sysdate-2,'D') then sysdate - 2
       when 2 = to_char(sysdate-3,'D') then sysdate - 3
       when 2 = to_char(sysdate-4,'D') then sysdate - 4
       when 2 = to_char(sysdate-5,'D') then sysdate - 5
       when 2 = to_char(sysdate-6,'D') then sysdate - 6
       when 2 = to_char(sysdate-7,'D') then sysdate - 7
  end as last_monday
from dual

Tom H · Answer 7 · 2008-10-27T19:30:38.573

-1

A T-SQL solution:

Assuming that SET DATEFIRST is at the default (Sunday = 7), last Monday's date:

SELECT
DATEADD(dy, DATEPART(dw, GETDATE()) - 9, GETDATE())

The "-9' is to go back one week (-7) and then since Monday is 2 we are subtracting 2 more and adding the day of the week for the current day.

edited Oct 27 '08 at 19:30

answered Oct 27 '08 at 15:54

Tom H

46,766
14
87
128

I tried this against 2008-10-28. With DateFirst = 1 it gave me 2008-10-21. With DateFirst = 7, it gave me 2008-10-22. Neither of those are mondays. – Amy B Oct 27 '08 at 16:12
SELECT @@DATEFIRST SELECT DATEADD(dy, DATEPART(dw, GETDATE()) - 9, GETDATE()) Results: ---- 7 (1 row(s) affected) ----------------------- 2008-10-20 15:31:35.583 (1 row(s) affected) – Tom H Oct 27 '08 at 19:31

SQL Date Formulas

7 Answers7