Oracle SQL TUTORIAL – Date columns with Time in Where condition

For training reach out to me at [email protected]

In this video, we will see how to retrieve records for a specific time duration.


SQLs used in this video are given below.

select * from employees;

alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';

SELECT DBMS_RANDOM.VALUE(0,9) FROM DUAL;

SELECT SYSDATE-DBMS_RANDOM.VALUE(0,9) FROM DUAL;

SELECT FIRST_NAME,LAST_NAME,
SYSDATE-DBMS_RANDOM.VALUE(0,9) CREATED_DATE FROM EMPLOYEES;

CREATE TABLE EMP_TEST AS
SELECT FIRST_NAME,LAST_NAME,
SYSDATE-DBMS_RANDOM.VALUE(0,9) CREATED_DATE FROM EMPLOYEES;


SELECT * FROM EMP_TEST ORDER BY CREATED_DATE DESC;


select sysdate from dual;

select sysdate,trunc(sysdate) from dual;

26 sep 2018 00:00:00 to 26 sep 2018 23:59:59

select sysdate,trunc(sysdate),trunc(sysdate)+0.99999 from dual;

Records that got created yesterday..

25 sep 2018 00:00:00 to 25 sep 2018 23:59:59


select trunc(sysdate-1),trunc(sysdate-1)+0.99999 from dual;

SELECT * FROM emp_test
WHERE created_date BETWEEN trunc(SYSDATE-1)
AND trunc(SYSDATE-1)+0.99999
order by created_date;

Retrieve records for 22nd Septembter 2018

SELECT * FROM emp_test
WHERE created_date BETWEEN to_date('22-sep-2018 00:00:00','dd-mon-yyyy hh24:mi:ss')
AND to_date('22-sep-2018 23:59:59','dd-mon-yyyy hh24:mi:ss')
order by created_date;


SELECT * FROM emp_test
WHERE created_date BETWEEN to_date('22-sep-2018','dd-mon-yyyy')
AND to_date('22-sep-2018 23:59:59','dd-mon-yyyy hh24:mi:ss')
order by created_date;

-- Get all records created in last 24 hours

select sysdate,sysdate-1 from dual;


SELECT * FROM emp_test
where created_date between sysdate-1 and sysdate order by created_date;