Getting Table Results based on a Start and End Date using a single date

Question

Currently I am having trouble trying to query a data set that contains multiple records for unique customers. Each row has a start date and end date and the most recent record marked as Most Current and end date as 12/31/9999. I am trying to write a query so that I can return all records that fall on a specific date such as months end (10/31/2018). My goal in Oracle SQL Developer is to type in all records/data on the day 10/31/2017 so the rows with the * are the only ones Id want back. The final outcome is to do this with a much larger file and see the entire year at months end where each person was at based on the month end date for 1 year.

Answer 1

I think you want to list any records that are current as of a specified date. To do this I'd simply use a BETWEEN condition:

select * from mytable
where date'2017-31-10' between start and end

Answer 2

Can you elaborate what output you are expecting? What i can see is that you are trying to implement temporal functionality here. Kindly check this link if this serves your purpose Oracle temporal . As for highlighted records, you can use a simple condition in where clause like this and format date part as required:

select * from emp where  endd=to_date('9999/12/31','yyyy/mm/dd')