trying to selecting rows that are between two date for a period of time

Question

This problem is like a brain teaser....so i have two dates, start date and end date and i want to select all the rows that are active between those two dates and if the period between those dates have passed then the rows should not be selected. Please note that these dates includes time

Here is an example before the code Start date = '15-MAY-2020 08:00 AM' and End date = '16-MAY-2020 08:00 AM'

As you can see there is 24 hour period between those dates and i want to select ALL ROWS THAT ARE BETWEEN THE START DATE AND END DATE and if the the time becomes '16-MAY-2020 08:01 AM' then those rows should not be displayed anymore.

Now the code

select id, title, color, start_date, end_date 
from colors
where end_date >= start_date and end_date <= sysdate

Summary: If a color is between a certain start and end then it must only be displayed during that start and end otherwise it should not be shown It is like eating ice cream, you get the ice cream and start to eat it (start) and you eat it until its is finished (end) then you have no more ice cream Same with the colors, it starts to show at a particular date and time and has a particular date and time when it ends after that the color does not show anymore

Answer 1

You are close. Both your conditions needs to reference sysdate:

select id, title, color, start_date, end_date 
from colors
where start_date <= sysdate and end_date >= sysdate