SQL Date Formulas

Question

I need a date formula in Oracle SQL or T-SQL that will return the date of the previous week (for example, the date on the last Monday).

I have reports with parameters that run every week, usually with parameter dates from Monday to Friday or Sunday-Saturday of the previous week. I would not want to enter dates when I run reports every week.

The data is in Oracle, and I use SQL Server 2005 Reporting Services (SSRS) for reporting.

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 some time 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

Here is the Oracle solution for Monday.

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

, .

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;

, 00:00:00, TRUNC (...).

Answer 3

. .

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

, , , .

Answer 4

, 8 .

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

:

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'

. . , .

Answer 5

(Oracle)

trunc (sysdate, 'IW') -

trunc (sysdate, 'IW') - 7 -

, ​​, "IW" ( ). , ​​...

trunc (sysdate, 'W') + 1 - ,

trunc (sysdate, 'W') + 1-7 -

Answer 6

Oracle:

:

: , .

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 7

T-SQL:

, SET DATEFIRST ( = 7), :

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

"-9" (-7), - 2 2 .