Home > Software design >  I need to get the last 7 days in Oracle SQL
I need to get the last 7 days in Oracle SQL

Time:06-07

I have a database with registration dates and I need to check who made a registration during the last 7 days. The table I use has the following shape:

create table GUEST(
    gNo          int, 
    gAT          varchar2(7)   unique, 
    gLastName    varchar2(40), 
    gFirstName   varchar(30), 
    gRegDate     date, 
    primary key(gNo)
);

insert into GUEST values
(150, 'A123987', 'NIKOLAOU', 'GEWRGIOS', TO_DATE('02/07/2022','DD/MM/YYYY'));

I've already tried this:

select gregdate 
from guest 
where gregdate <= sysdate - 7;

though it seems like it substracts 7 from month instead of day.

Can you help me with this issue?

CodePudding user response:

This query should get the days you need:

SELECT gregdate 
FROM guest 
WHERE gregdate > sysdate - 7 
  AND gregdate <= sysdate 

The conditions will enclose the gregdate field in range (sysdate - 7, sysdate], where the former boundary is not included while the latter one is.

CodePudding user response:

The condition here is wrong. It should be sysdate - gregdate <=7 or gregdate > sysdate -7.

For clarity, The 7 is subtracted from the day (not from months).

You should also add check whether the gregdate is greater than the current date.

  • Related