SQL Date Filtering Examples
Here are several examples of SQL queries that demonstrate different ways to filter data based on date conditions using the actual date range in your employees table (You must install the hr sample schema as pre-requisite):
Basic Date Range Queries
|
1 2 3 4 |
-- Find employees hired in 2015 SELECT first_name, last_name, hire_date FROM employees WHERE hire_date BETWEEN '01-JAN-15' AND '31-DEC-15'; |
|
1 2 3 4 |
-- Find employees hired in the first quarter of 2014 SELECT first_name, last_name, hire_date FROM employees WHERE hire_date BETWEEN '01-JAN-14' AND '31-MAR-14'; |
|
1 2 3 4 |
-- Find employees hired in the second half of 2017 SELECT first_name, last_name, hire_date FROM employees WHERE hire_date BETWEEN '01-JUL-17' AND '31-DEC-17'; |
Using Comparison Operators with Dates
|
1 2 3 4 |
-- Find employees hired after 2016 SELECT first_name, last_name, hire_date FROM employees WHERE hire_date > '31-DEC-16'; |
|
1 2 3 4 |
-- Find employees hired before 2013 SELECT first_name, last_name, hire_date FROM employees WHERE hire_date < '01-JAN-13'; |
|
1 2 3 4 |
-- Find employees hired on or after June 2017 SELECT first_name, last_name, hire_date FROM employees WHERE hire_date >= '01-JUN-17'; |
Using Date Arithmetic
|
1 2 3 4 |
-- Find employees hired exactly one month before a specific date SELECT first_name, last_name, hire_date FROM employees WHERE hire_date + 30 = '15-MAR-16'; |
|
1 2 3 4 |
-- Find job history records where the employee worked for more than 365 days SELECT employee_id, start_date, end_date, (end_date - start_date) AS days_worked FROM job_history WHERE (end_date - start_date) > 365; |
Combining Date Conditions with Other Filters
|
1 2 3 4 5 |
-- Find employees hired in 2012 who work in department 60 SELECT first_name, last_name, hire_date, department_id FROM employees WHERE hire_date BETWEEN '01-JAN-12' AND '31-DEC-12' AND department_id = 60; |
|
1 2 3 4 5 |
-- Find employees hired in 2016 with a salary greater than 8000 SELECT first_name, last_name, hire_date, salary FROM employees WHERE hire_date BETWEEN '01-JAN-16' AND '31-DEC-16' AND salary > 8000; |
|
1 2 3 4 5 |
-- Find employees hired in 2013 whose names start with 'S' SELECT first_name, last_name, hire_date FROM employees WHERE hire_date BETWEEN '01-JAN-13' AND '31-DEC-13' AND first_name LIKE 'S%'; |
Working with Multiple Date Ranges Using IN and OR
|
1 2 3 4 5 |
-- Find employees hired in either 2011 or 2018 SELECT first_name, last_name, hire_date FROM employees WHERE (hire_date BETWEEN '01-JAN-11' AND '31-DEC-11') OR (hire_date BETWEEN '01-JAN-18' AND '31-DEC-18'); |
|
1 2 3 4 |
-- Find employees NOT hired in 2014 (using NOT BETWEEN) SELECT first_name, last_name, hire_date FROM employees WHERE hire_date NOT BETWEEN '01-JAN-14' AND '31-DEC-14'; |
Specific Dates for Your Database
|
1 2 3 4 |
-- Find employees hired in the first month of the database records SELECT first_name, last_name, hire_date FROM employees WHERE hire_date BETWEEN '13-JAN-11' AND '31-JAN-11'; |
|
1 2 3 4 |
-- Find employees hired in the most recent month of data SELECT first_name, last_name, hire_date FROM employees WHERE hire_date BETWEEN '01-APR-18' AND '21-APR-18'; |
|
1 2 3 4 |
-- Find employees hired during the first year in the database SELECT first_name, last_name, hire_date FROM employees WHERE hire_date BETWEEN '13-JAN-11' AND '13-JAN-12'; |
These examples demonstrate various techniques for filtering data based on date conditions using the actual date range in your employees table. Students can practice these queries to better understand how to retrieve specific date-related information from database tables.
