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