Extracting the Date Portion from a SQL Server DateTime Value

SQL Server’s DATETIME and DATETIME2 data types store both date and time information. Often, you need to work with only the date part of a datetime value, for example, when grouping data by day or comparing dates without considering the time. This tutorial outlines several methods to extract the date portion from a SQL Server datetime value.

Understanding the Requirements

Let’s assume you have a datetime value like 2008-09-22 15:24:13.790 and you want to obtain just 2008-09-22 00:00:00.000 or, even better, a DATE data type without any time component. We’ll explore several approaches, each with its trade-offs.

Method 1: Using `CONVERT` with Style Code

One of the simplest methods is to use the CONVERT function along with a style code. This allows you to format the datetime value as a string with only the date part.

SELECT CONVERT(VARCHAR(10), GETDATE(), 111);

This query converts the current datetime (GETDATE()) to a VARCHAR string in the format YYYY/MM/DD. The 111 style code specifies this format. You can find a comprehensive list of style codes in the official Microsoft SQL Server documentation. Keep in mind that this approach returns a string, not a DATE data type.

Important: Using VARCHAR to store dates can impact sorting and indexing performance. It’s generally preferable to work with native date data types when possible.

Method 2: Using `DATEADD` and `DATEDIFF`

This method avoids string conversions and operates directly on the datetime value. It works by calculating the difference in days between the datetime value and a base date (midnight of January 1, 1900) and then adding that difference back to midnight of the same day.

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()));

Here’s how it works:

DATEDIFF(dd, 0, GETDATE()): Calculates the number of days between midnight (represented by 0) and the current datetime (GETDATE()).
DATEADD(dd, 0, ...): Adds zero days to midnight, effectively truncating the time portion and returning a DATETIME value with the time set to 00:00:00.000.

This method is efficient as it doesn’t rely on string conversions. However, it returns a DATETIME data type, not a true DATE type.

Method 3: Casting to `DATE` (SQL Server 2008 and Later)

SQL Server 2008 introduced the DATE data type, which stores only the date without any time component. You can directly cast a DATETIME value to a DATE using CAST or CONVERT.

SELECT CAST(GETDATE() AS DATE);

-- Or using CONVERT
SELECT CONVERT(DATE, GETDATE());

This is the most straightforward and recommended approach for SQL Server 2008 and later versions. It provides a native date data type, ensuring efficient storage, sorting, and indexing.

Performance Considerations

While all methods achieve the desired result, performance can vary.

CONVERT to VARCHAR: Generally the slowest due to string conversions.
DATEADD and DATEDIFF: More efficient than string conversions, but still involves some overhead.
CAST or CONVERT to DATE: The most efficient, especially when working with large datasets, as it leverages the native DATE data type.

It’s always a good practice to test different methods with your specific data and workload to determine the most optimal solution. Use SQL Server Management Studio’s execution plan feature to analyze performance.