Summary -

In this topic, we described about the Select Date with detailed example.

SELECT DATE is used to fetch matching data with specified date in WHERE clause.

Syntax -

SELECT column1, column2, …, columnN
FROM table_name
WHERE date_column = "date_in_table";
  • column1, column2, …, columnN - Specifies the name of the columns.
  • table_name - Specifies the name of the table.
  • date_column - Specifies the name of the column which have the date.

Example -

Let us consider below table(s) as an example table(s) to frame the SQL query for getting the desired results.

employee_details -

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 45000.00 1000
002 Employee2 Director 2019-07-11 40000.00 2000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000
008 Employee8 Salesman Employee4 2019-09-09 14000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000

Scenario – Fetch rows using SELECT DATE.

Requirement - Fetch employees who joined on '2019-09-09' from employee_details table. The query was as follows – 

SELECT * FROM employee_details WHERE date_of_hire = "2019-09-09";

By executing above query, we can get results as shown below -

emp_id emp_name designation manager_id date_of_hire salary dept_id
008 Employee8 Salesman Employee4 2019-09-09 14000.00 2000

Scenario – Fetch rows using SELECT DATE with BETWEEN clause.

Requirement - Fetch employees who joined between '2019-07-11' and '2019-09-09' from employee_details table. The query was as follows – 

SELECT * FROM employee_details
WHERE date_of_hire 
BETWEEN 2019-07-11 AND '2019-09-09';

By executing above query, we can get results as shown below –

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 45000.00 1000
002 Employee2 Director 2019-07-11 40000.00 2000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000
008 Employee8 Salesman Employee4 2019-09-09 14000.00 2000

Scenario – Fetch rows using SELECT DATE with BETWEEN clause.

Requirement - Fetch employees who joined in 2019 from employee_details table. The query was as follows – 

SELECT * FROM employee_details WHERE date_of_hire LIKE '2019%';

By executing above query, we can get results as shown below –

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 45000.00 1000
002 Employee2 Director 2019-07-11 40000.00 2000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000
008 Employee8 Salesman Employee4 2019-09-09 14000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000