SQL - SELECT DATE

SQL (Structured Query Language) can work with datetime data types. SELECT DATE is an important concept when retrieving records that filter data based on date. Whether you work with employee records, transaction records, or product sales, modifying and retrieving date data is often important for your SQL queries.

In this article, we’ll explore how to use the SELECT DATE functionality in SQL. We will explain how to retrieve and manipulate date values using SQL queries, including DATE, DATETIME, TIMESTAMP, and related SQL functions.

SQL SELECT DATE

In SQL, date and time values are stored in DATE, DATETIME, TIME, TIMESTAMP, and YEAR data types, depending on your use case and the database system you're using. To retrieve these values, we use the SELECT statement.

Syntax:

SELECT column_name

FROM table_name

WHERE date_column = 'YYYY-MM-DD';

• column_name: The column from which you want to retrieve the data.
• table_name: The table where the date column resides.
• date_column: The column that contains date values.
• 'YYYY-MM-DD': The date format used to filter the records.

Example of SQL SELECT DATE

Let's create an Employee table with a column hire_date of type DATE, which stores the hiring date of employees.

CREATE TABLE Employee (
    EmpID INT,
    FirstName VARCHAR(255),
    LastName VARCHAR(255),
    HireDate DATE
);
INSERT INTO Employee (EmpID, FirstName, LastName, HireDate)
VALUES
(1, 'John', 'Doe', '2022-01-15'),
(2, 'Jane', 'Smith', '2021-06-30'),
(3, 'Alice', 'Johnson', '2020-08-25');

Output:

Example: Select Employees Hired on a Specific Date

SELECT FirstName, LastName, HireDate
FROM Employee
WHERE HireDate = '2022-01-15';

Output:

This SQL query retrieves the FirstName, LastName, and HireDate of the employee who was hired on 2022-01-15.

Using SQL SELECT Date Functions for Filtering

These SQL functions allow you to extract specific components (like year, month, or day) from a DATE or DATETIME column.

• YEAR(): Extracts the year from a date.
• MONTH(): Extracts the month from a date.
• DAY(): Extracts the day from a date.

Example 1: Select Employees Hired in a Specific Year

SELECT FirstName, LastName, HireDate
FROM Employee
WHERE YEAR(HireDate) = 2021;

Output:

This query retrieves all employees hired in the year 2022.

Example 2: Select Employees Hired in a Specific Month

SELECT FirstName, LastName, HireDate
FROM Employee
WHERE MONTH(HireDate) = 6;

Output:

Advanced Date Functions

In addition to extracting parts of a date, SQL provides several advanced date functions for more complex operations. Below are some common advanced functions:

CURDATE() and NOW()

• CURDATE(): Returns the current date (without the time component).
• NOW(): Returns the current date and time.

Example 1: Select Employees Hired Before Today

SELECT FirstName, LastName, HireDate
FROM Employee
WHERE HireDate < CURDATE();

This query retrieves all employees hired before today’s date.

Using SQL BETWEEN for Date Ranges

The BETWEEN operator allows you to filter records within a specified date range. This can be useful when you need to retrieve data between two specific dates, such as all transactions made within a certain period or all employees hired during a specific quarter.

Example 1: Select Employees Hired Between Two Dates

SELECT FirstName, LastName, HireDate
FROM Employee
WHERE HireDate BETWEEN '2021-01-01' AND '2022-01-01';

Output:

Conclusion

SELECT DATE is an essential concept in SQL, especially when dealing with time-based data. By mastering SQL date functions, such as YEAR(), MONTH(), DAY(), CURDATE(), DATE_ADD(), and DATE_SUB(), you can effectively filter, manipulate, and retrieve date and time data based on your requirements. With the right use of these functions, you can write efficient SQL queries for tasks ranging from simple date comparisons to advanced time-based analysis. Whether you're tracking sales, employee records, or any other time-sensitive data, understanding how to use dates in SQL queries will help you extract the precise data you need.