Query for LAST WEEK starting from monday-CodePudding

I need your help please .

I'm using SAP HANA for B1

I have a table for Invoice = OINV TO

 SELECT T0."DocNum" , T0."DocDate" FROM OINV T0

I want a query that get the invoice from last week starting from Monday . For example , we are in Friday 16/09/2022 , i want the query to show me INVOICE from last weeks starting from Monday 05/09/2022 to 11/09/2022

Regards

CodePudding user response：

SQL will be like this

SELECT * FROM {tableName} WHERE {columnThatRecordsDate} > '2022-09-05' and {columnThatRecordsDate} < '2022-09-11'

edit for OP comment

SELECT * FROM {tableName} WHERE YEARWEEK(columnThatRecordsDate) = YEARWEEK(NOW() - INTERVAL 1 WEEK);

CodePudding user response：

There are multiple solutions to this. IMHO the easiest is to use functions WEEK or ISOWEEK. When going forward with WEEK, you also need to consider checking for the correct year, which is why I would prefer this version:

CREATE TABLE TEST (DOCNUM INTEGER, DOCDATE DATE);

INSERT INTO TEST VALUES (1, '15.9.2022');
INSERT INTO TEST VALUES (2, '8.9.2022');
INSERT INTO TEST VALUES (3, '1.9.2022');

SELECT * 
FROM TEST
WHERE ISOWEEK(CURRENT_DATE) = ISOWEEK(ADD_DAYS(DOCDATE,7))

Note, that the year is part of the output of ISOWEEK.