Retrieving dates using SELECT DATE command in SQL

SQL

In SQL when it comes to working with dates, the SELECT DATE, command is very useful for a variety of tasks. This command helps to find today’s date, Filter data after a specific date, Format dates neatly and many more.

Basic Syntax

The basic syntax for a simple SQL SELECT statement involving dates is as follows:

SELECT column1, column2, ...
FROM table
WHERE date_condition;
  • SELECT: Specifies the columns you want to retrieve.
  • FROM: Specifies the table from which to retrieve the data.
  • WHERE: Optional clause to filter the results based on specified conditions. In this clause generally we have to write conditions which are based on Dates.

Example: Retrieving Current Date and Time

To fetch the current date and time, you can use a function such as GETDATE() in SQL Server or CURRENT_TIMESTAMP in MySQL:

SELECT GETDATE() AS CurrentDateTime;

This will return a single-column result with the current date and time.

CurrentDateTime

2024-02-04 12:30:45

Practical scenario,

Database: Students

StudentIDFirstNameLastNameEnrollmentDate
1JohnDoe2023-01-15
2JaneSmith2023-02-20
3AlexJohnson2023-03-10
4EmmaDavis2023-04-05

Example 1: Selecting Specific Columns with Date Conditions

SELECT FirstName, LastName, EnrollmentDate
FROM Students
WHERE EnrollmentDate > '2023-02-01';

Output:

FirstNameLastNameEnrollmentDate
JaneSmith2023-02-20
AlexJohnson2023-03-10
EmmaDavis2023-04-05

Example 2: Formatting Date Output

In this example, we demonstrate how to format the date output using the FORMAT() function

SELECT FirstName, LastName, FORMAT(EnrollmentDate, 'MM/dd/yyyy') AS FormattedDate
FROM Students;
FirstNameLastNameFormattedDate
JohnDoe01/15/2023
JaneSmith02/20/2023
AlexJohnson03/10/2023
EmmaDavis04/05/2023

Example 4: Calculations with Dates

SELECT FirstName, LastName, DATEADD(DAY, 30, EnrollmentDate) AS EnrollmentDateAfter30Days
FROM Students;
FirstNameLastNameEnrollmentDateAfter30Days
JohnDoe2023-02-14
JaneSmith2023-03-22
AlexJohnson2023-04-09
EmmaDavis2023-05-05

Example 5: Finding the Oldest and Newest Enrollment Dates

SELECT MIN(EnrollmentDate) AS OldestEnrollmentDate, MAX(EnrollmentDate) AS NewestEnrollmentDate
FROM Students;
OldestEnrollmentDateNewestEnrollmentDate
2023-01-152023-04-05

Example 6: Extracting Month and Year from Enrollment Dates

SELECT FirstName, LastName, MONTH(EnrollmentDate) AS EnrollMonth, YEAR(EnrollmentDate) AS EnrollYear
FROM Students;
FirstNameLastNameEnrollMonthEnrollYear
JohnDoe12023
JaneSmith22023
AlexJohnson32023
EmmaDavis42023

Example 7: Identifying Students Enrolled in the Last 3 Months

SELECT FirstName, LastName, EnrollmentDate
FROM Students
WHERE EnrollmentDate >= DATEADD(MONTH, -3, GETDATE());
FirstNameLastNameEnrollmentDate
AlexJohnson2023-03-10
EmmaDavis2023-04-05

Example 8: Counting Students Enrolled Each Year

SELECT YEAR(EnrollmentDate) AS EnrollmentYear, COUNT(StudentID) AS StudentCount
FROM Students
GROUP BY YEAR(EnrollmentDate);
EnrollmentYearStudentCount
20234