How to Select a Date Without Including the Time in SQL

1. Overview

Generally, for efficient storage and data manipulation in SQL, the DATE and TIME data types are mostly useful. Nevertheless, some cases might require us to extract only DATE from a DATETIME value instead of both.

In this article, we’ll show different ways to do this in SQL. Moreover, we’ll use the Baeldung University schema and data to show these practical examples. Specifically, we’ll use the student table for demonstrations in this article.

2. Understanding DATE and TIME Data Types in SQL

We use the DATE and TIME data types to store temporal data, including dates, times, and timestamps. However, the way several SQL database systems provide data types to handle this data varies. Here, we’re using MYSQL as a case study.

Let’s examine what makes up the DATE and TIME data types in MySQL:

• DATE: stores values for year, month, and day in the format YYYY-MM-DD
• TIME: stores values for time in the format HH:MM:SS
• DATETIME: stores values for DATE and TIME in the format YYYY-MM-DD HH:MI:SS
• TIMESTAMP: automatically updates DATE and TIME records when modified, in the format YYYY-MM-DD HH:MI:SS
• YEAR: stores year values in the format YYYY or YY

These data types are important and serve various purposes when managing data in MySQL databases.

So, how do we select DATE in SQL without getting both the DATE and TIME values? This is possible through various methods, which include using the DATE(), CAST(), and DATE_FORMAT() functions.

3. Using the DATE() Function

In cases where we wish to consider only the dates when working with a group of data, this function can help. We can use the DATE() function to get only dates from a column with a DATETIME value.

For example, let’s show how to use the DATE() function to extract only a date:

SELECT DATE(graduation_datetime) AS date_only FROM Student;
+------------+
| date_only  |
+------------+
| 2027-06-30 |
| 2024-06-30 |
...

The query effectively retrieves only the DATE part from the graduation_datetime column.

4. Using the CAST() Function

Additionally, we can utilize the CAST() function to retrieve only dates from a DATETIME column in SQL. This function achieves this by effectively converting the DATETIME value to just the DATE equivalent.

For example, let’s show how the CAST() function retrieves only dates in SQL:

SELECT CAST(graduation_datetime AS DATE) AS date_only FROM Student;
+------------+
| date_only  |
+------------+
| 2027-06-30 |
| 2024-06-30 |
...

Here, we extracted only the dates by specifying the target data type, which is the graduation_datetime column. Then, the CAST() function converts the entire value in that column to dates only.

5. Using the DATE_FORMAT() Function

Apart from using the DATE_FORMAT() function to format DATETIME and timestamp values, we can also utilize it to extract only the DATE part in SQL. We do this by formatting the DATETIME value to display only dates in our query.

For example, let’s explore how to use the DATE_FORMAT() function to select a date without including the time in SQL:

SELECT DATE_FORMAT(graduation_datetime, '%Y-%m-%d') AS date_only FROM Student;
+------------+
| date_only  |
+------------+
| 2027-06-30 |
| 2024-06-30 |
...

Basically, %Y-%m-%d in this query specifies the format for the DATE part (YYYY-MM-DD). Further, it efficiently formats and retrieves only the DATE part of graduation_datetime column.

6. Conclusion

In this article, we looked into the different ways to extract dates without including time  in SQL. Specifically, in MySQL, the ways include using the DATE(), CAST(), and DATE_FORMAT() functions.

Using these functions enables us to easily extract only the DATE portion in SQL. Also,  it saves time and enables accurate data presentation.