By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'
CREATE TABLE table_name ( start_date DATE )
INSERT INTO table_name ( start_date )
SELECT DATE '2021-06-13' + INTERVAL '00:00:00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT DATE '2021-06-13' + (SYSDATE - TRUNC(SYSDATE)) FROM DUAL UNION ALL
SELECT DATE '2021-06-13' + (SYSDATE - TRUNC(SYSDATE)) + INTERVAL '1' SECOND FROM DUAL UNION ALL
SELECT DATE '2021-06-13' + INTERVAL '23:59:59' HOUR TO SECOND FROM DUAL;
4 rows affected
SELECT *
FROM table_name
WHERE start_date >= DATE '2021-01-01'
AND start_date < TRUNC( SYSDATE, 'IW' )
| START_DATE |
|---|
| 2021-06-13 00:00:00 |
| 2021-06-13 19:48:47 |
| 2021-06-13 19:48:48 |
| 2021-06-13 23:59:59 |
SELECT *
FROM table_name
WHERE start_date >= TRUNC( SYSDATE, 'YYYY' )
AND start_date < TRUNC( SYSDATE, 'IW' )