SQL Server DateTime To Date

As a Senior SQL database developer, I’ve encountered various scenarios where I needed to extract just the date portion from a DateTime value in SQL Server. I’ll walk you through different methods to convert DateTime to Date format in SQL Server with examples in this article.

Table of Contents

SQL Server derives all system date and time values from the computer’s operating system on which the instance of SQL Server runs. This is important to remember when working with date conversion functionality.

Now, let us discuss all the methods individually.

Method 1: Using the CONVERT Function

The CONVERT function is one of the basic ways to convert the DateTime values to date-only format in SQL Server. The below simple query will return today’s date without the time component.

SELECT CONVERT(DATE, GetDate()) AS ConvertedDate;

After executing the above query, I got the expected output, as shown in the below screenshot.

If you want the date to be displayed in a particular format, for example, to get the date in MM/DD/YYYY format, we can execute the below query.

SELECT CONVERT(VARCHAR, GetDate(), 101) AS FormattedDate;

After executing the above query, I got the expected output, as shown in the screenshot below.

Common CONVERT Style Codes for USA Date Formats

Style Code	Format	Example Output
101	MM/DD/YYYY	04/02/2025
110	MM-DD-YYYY	04-02-2025
111	YYYY/MM/DD	2025/04/02
112	YYYYMMDD	20250402

Method 2: Using the CAST Function

Another straightforward approach is to use the CAST function for this purpose.

CAST function is often preferred for its simplicity and readability when converting a DateTime to a Date format. Unlike CONVERT, CAST doesn’t allow formatting options, but it’s perfect when you need the date portion without any specific formatting.

SELECT CAST(GetDate() AS DATE) AS CastedDate;

After executing the above query, I got the expected output, as shown in the screenshot below.

Method 3: Using the DATE Data Type

SQL Server 2008 introduced the DATE data type, which stores only the date information without the time components.

DECLARE @DateTimeValue DATETIME = '2025-04-02 14:30:25';
SELECT @DateTimeValue AS OriginalDateTime, CAST(@DateTimeValue AS DATE) AS DateOnly;

After executing the above query, I got the expected output successfully.

how to get only date from datetime in sql

Method 4: Using DATEADD and DATEDIFF Functions

A clever approach to strip time components is using the DATEADD and DATEDIFF functions together.

SELECT DATEADD(day, DATEDIFF(day, 0, GetDate()), 0) AS DateOnly;

After executing the above query, I got the expected output, as shown in the below screenshot.

how to get only date from datetime in sql server

Method 5: Using the FORMAT Function

For SQL Server 2012 and later versions, the FORMAT function provides flexible date formatting options like the below query.

SELECT FORMAT(GetDate(), 'yyyy-MM-dd') AS FormattedDate;

After executing the above query, I got the expected output, as shown in the below screenshot.

how to get only date in sql server without time

Note:

CAST and CONVERT are generally the most efficient methods
The FORMAT function offers excellent formatting flexibility but at a performance cost
For filtering operations, avoid functions on indexed columns whenever possible

Conclusion

Converting DateTime to Date in SQL Server is a common requirement that can be addressed using multiple methods. The method you choose should depend on your specific formatting and performance requirements.

We can choose the CAST(DateTime AS DATE) function for simple date extraction with no specific formatting needs. For more complex formatting requirements, CONVERT with style codes or the FORMAT function (for SQL Server 2012+) provide greater flexibility.