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.

Answer 1

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. */

Answer 2

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

Answer 3

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.

Answer 4

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.

Answer 5

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

Answer 6

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(...).

Answer 7

(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