Oracle SQL TUTORIAL – Date columns with Time in Where condition

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;

[ajaybind]

wow this best video I found with full explanation..
great sir.. thanku sir 💯💝

[Sam-qp]

Hi Sir, what can be the query for the extension of effective end date of the employee

[krishnagatla]

I want to get data from 23-mar-23 18:00 to 24-mar-23 18:00 give me a solution

[ankurrathour]

How to get hourly records from one date to another date... Like how many orders we got every hour from 3rd March to 4th march.... Can u plz help me with the query

[jerrypauljohn]

Nice work Sir .... Really helpful for my interview preparations.... Keep posting more !!

[Moriarity]

Hi Ganesh Sir..I have two tables..one has a Fiscal Month Description ( Jan-21, Feb-21) and in same table it has date range 1-Jan to 30-Jan as fiscal month 1-Feb- to 28-Feb as Feb 21. In second Table of Orders has Entry date..I am trying to find out the Description for each row in Orders. Any ideas please help
Thanks in advance.

[ce]

how do I take input HH:MM or HH:MM:SS in oracle sql? need only time, and all the datatypes are giving date & time.

[Moriarity]

Instead of 22-Sep-2018 if I need to use a column name what should I use as syntax ? Thank you for video and your response in advance

[apu]

Hi Ganesh, how to get the last 2 hrs data

[mohankumarganesan]

Sir, I need data query for before one month using automatically, not put the date manually.

[balpreetsingh]

If i only need to write the year of the date then what will be the code

[PrishaPundir_]

I want to fetch the data between 5 am to 5 am..please tell me how to get

[magneshpoojary]

I have a question if the column in a table has datetime and I want total sum of amount for the transactions that has happened between 6am to 6pm only for past 1month.

Edit: SQL server 14 ssms 17

[sonalprabhu]

Hello sir, how to get the data for last 10 min ?