I would like to find the record with the max update_date of all records with update_date equal or less then a given date(format dd.mm.yyyy) (update_date <= :a_given_date).
create table client (client_id number, tenant varchar2(2), client_name varchar2(100), update_date timestamp);
insert into client values(1000, 'FR', 'Michelin', systimestamp-4);
insert into client values(1000, 'FR', 'Michelin', systimestamp-3);
insert into client values(1000, 'FR', 'Michelin', systimestamp-3);
insert into client values(1000, 'FR', 'Michelin', systimestamp-2/24);
insert into client values(1000, 'FR', 'Michelin', systimestamp-1/24);
insert into client values(1000, 'FR', 'Michelin', systimestamp);
commit;
SQL> create table client (client_id number, tenant varchar2(2), client_name varchar2(100), update_date timestamp);
Table created.
SQL>
SQL> insert into client values(1000, 'FR', 'Michelin', systimestamp-4);
1 row created.
SQL> insert into client values(1000, 'FR', 'Michelin', systimestamp-3);
1 row created.
SQL> insert into client values(1000, 'FR', 'Michelin', systimestamp-3);
1 row created.
SQL> insert into client values(1000, 'FR', 'Michelin', systimestamp-2/24);
1 row created.
SQL> insert into client values(1000, 'FR', 'Michelin', systimestamp-1/24);
1 row created.
SQL> insert into client values(1000, 'FR', 'Michelin', systimestamp);
1 row created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> select *
2 from client
3 where update_date < date '2019-09-26'
4 order by update_date desc
5 fetch first 1 rows only;
CLIENT_ID TE CLIENT_NAME UPDATE_DATE
---------- -- -------------------- ---------------------------------------------------------------------------
1000 FR Michelin 24-SEP-19 12.26.12.000000 PM