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.
Share your thoughts in the comments
Please Login to comment...