Skip to content
Related Articles

Related Articles

Improve Article
Save Article
Like Article

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

  • Last Updated : 28 Oct, 2021

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’.

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.

My Personal Notes arrow_drop_up
Recommended Articles
Page :

Start Your Coding Journey Now!