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.