SQL Query to Convert DateTime to Date in SQL Server

In SQL Server, working with DateTime data types can be a bit complex for beginners. This is because DateTime includes both the date and time components, while many scenarios only require the date. Whether you're working with large datasets, performing data analysis, or generating reports where time does not matter, converting DateTime to Date becomes an important task.

In this article, we will explain how to convert DateTime to Date in SQL Server using four powerful methods: CAST(), CONVERT(), TRY_CONVERT(), and SUBSTRING(). By the end of this article, we will understand how to extract the date part of a DateTime value and streamline your queries.

Why Convert DateTime to Date in SQL Server?

When working with DateTime values, there are many situations where the time component is irrelevant or unnecessary. Converting DateTime to Date can be helpful in scenarios like:

• Generating Date-Based Reports: If you're building daily, weekly, or monthly reports, you may not need to include the time portion.
• Simplifying Date Comparisons: When you need to compare dates without worrying about time, converting to just the date helps streamline the process.
• Aggregating by Date: For example, when calculating daily totals or averages, you may want to group data by date only, ignoring the time part.

Methods to Convert DateTime to Date in SQL Server

The aim of this article data is to convert DateTime to Date in SQL Server like YYYY-MM-DD HH:MM: SS to YYYY-MM-DD. Here are four common and effective methods to convert DateTime to Date in SQL Server:

1. Using CAST()

The CAST() function in SQL Server is one of the most flexible functions available. It allows you to explicitly convert one data type to another, and it's perfect for stripping off the time portion from a DateTime value. To convert a DateTime to a Date, we can use the CAST() function as shown below.

Syntax

CAST( dateToConvert AS DATE)

Example 1: Convert the current DateTime to a Date.

SELECT CAST(GETDATE() AS DATE) AS CURRENT_DATE

Output

Explanation: In this example, GETDATE() returns the current date and time, and the CAST() function converts it into a DATE type.

Example 2: Convert a specific DateTime string to a Date.

SELECT CAST('2021-08-27 17:26:36.710' AS DATE) AS CURRENT_DATE_GFG

Output

Explanation: Here, the CAST() function is used to convert a string representation of DateTime to a Date, removing the time component.

2. Using CONVERT()

The CONVERT() function is another SQL Server function that allows you to convert data types. It allows us to specify the target data type as DATE and is particularly useful when working with different date formats.

Syntax

CONVERT(DATE, dateToConvert)

Example 1: Convert the current DateTime to a Date.

SELECT CONVERT(DATE, GETDATE()) AS CURRENT_DATE_GFG

Output

Explanation: CONVERT() works similarly to CAST() but allows for different formatting options, making it a great choice when working with DateTime values in different formats.

Example 2: Convert a specific DateTime string to a Date.

SELECT CONVERT(DATE, '2021-08-27 17:26:36.710' ) AS CURRENT_DATE_GFG

Output

Explanation: The CONVERT() function efficiently converts the string DateTime to a Date, again discarding the time portion.

3. Using TRY_CONVERT()

The TRY_CONVERT() function is similar to CONVERT(), but with one key difference: if the conversion fails it returns NULL instead of throwing an error. This can be particularly useful when we are working with data integrity and error handling. This makes it ideal for scenarios where you’re working with data that might not always be in the correct format or might have unexpected values.

Syntax

TRY_CONVERT(DATE, dateToConvert)

Example 1: Convert the current DateTime to Date

SELECT TRY_CONVERT(DATE,GETDATE()) AS CURRENT_DATE_GFG

Output

Explanation: Like CONVERT(), TRY_CONVERT() can convert a DateTime value to Date, but it has the added advantage of safely handling data conversion errors.

Example 2: Convert a valid DateTime string to Date.

SELECT TRY_CONVERT(DATE,'2021-08-27 17:26:36.710') AS CURRENT_DATE_GFG

Output

Explanation: TRY_CONVERT() returns the same result as CONVERT() in this case, but it ensures that if the DateTime string was invalid or improperly formatted, the query would return NULL instead of an error.

4. Using SUBSTRING()

If we want to manually extract the date portion of a DateTime value as a string, you can use the SUBSTRING() function. This method involves getting a substring of the DateTime string, starting from the 0th index and selecting only the first 10 characters (YYYY-MM-DD). This method is not the most common approach, but it can be useful in certain situations

Syntax

SUBSTRING( dateToConvert ,0,11)

Example 1: Extract the date from a DateTime string.

SELECT SUBSTRING( '2021-08-27 17:26:36.710' ,0,11) AS CURRENT_DATE_GFG

Output

Explanation: Here, the SUBSTRING() function extracts the first 10 characters (the date part) of the DateTime string. This method can be useful when working with formatted strings.

Example 2: Extract the date from the current DateTime

SELECT SUBSTRING( CONVERT(varchar(17), GETDATE(), 23) ,0,11) AS CURRENT_DATE_GFG

Output

Explanation: In this example, we first CONVERT() the current DateTime to a varchar string with format 23 (YYYY-MM-DD), then use SUBSTRING() to get only the date part.

Conclusion

Converting DateTime to Date in SQL Server is a straightforward process that can help simplify our queries by focusing only on the date portion of the value. Whether we're using CAST(), CONVERT(), TRY_CONVERT(), or SUBSTRING(), each method allows us to effectively manage DateTime values in our SQL queries. By choosing the appropriate method for our use case, we can enhance query performance, improve data analysis, and generate accurate reports without the need for time-related data.