How to Retrieve the Records Based on a Date from Oracle Database?

DATE is a SQL-defined data type that is used to store dates in a column, different database management systems store dates in different formats. MySQL uses 3 bytes to store dates. It stores DATE in a format like ‘YYYY-MM-DD’. The DATE value ranges from 1000-01-01 to 9999-12-31. Oracle database management system stores DATE in a format like ‘MM-DD-YYYY’. 

Here ‘MM’ is month, ‘DD’ is day and ‘YYYY’ is the year. Microsoft SQL Server stores date in the same format as MySQL stores it. 

Step 1: Create a database

Query:

CREATE DATABASE geeksforgeeks;

Step 2: Let’s create a table named employees.

Query:

CREATE TABLE employee
(emp_id INT, emp_fname VARCHAR(45), 
emp_lname VARCHAR(45),join_date DATE, 
PRIMARY KEY(emp_id));

Step 3: Now we will insert values in the employee table. 

Query:

INSERT INTO employee VALUES 
(100181, 'Utkarsh', 'Tyagi', '1996-01-31'),
 (100182, 'Abhishek', 'Singh', '1996-03-21'), 
(100183, 'Jake', 'Johnson', '1997-06-14'), 
(100184, 'Abhimanyu', 'Rana', '1997-11-06'), 
(100185, 'Akansha', 'Sharma', '1998-11-23'), 
(100186, 'Priya', 'Yadav', '1999-10-10'),
(100187, 'Naina', 'Singhal', '2000-06-13');

Step 4: Now let’s see the contents of our table.

Query:

SELECT * FROM employee;

Output:

Step 5: We want to print only those records whose join_date is ‘2000-06-13’.

• Retrieving records based on DATE using the ‘=’ operator.

Query:

SELECT * FROM employee WHERE join_date = '2000-06-13';

Output:

We have only one employee whose join_date is on ‘2000-06-13’.

• Retrieving records based on DATE using LIKE keyword

We will be using the same employee table that we have used in the above example. Now our task is to retrieve those records whose join_date is in the year 1996 irrespective of day and month.

Query:

SELECT * FROM employee WHERE join_date LIKE '1996-%';

So we have two records whose join_date is in the year 1996.

•  Retrieving records based on DATE using BETWEEN keyword

We want to retrieve those records where join_date in between years 1996 and 1997.

Query:

SELECT * FROM employee WHERE join_date BETWEEN '1996-01-01' AND '1997-12-31';

So there are four employees who joined the company between that time interval.