How to Extract Date from SQL DateTime

The most straightforward way to extract only the date part from a SQL Server datetime value is by using the CAST() or CONVERT() function to convert the datetime to a date type, or by using the DATEFROMPARTS() function. For example, CAST(GETDATE() AS DATE) will return just the date portion, while CONVERT(DATE, GETDATE()) achieves the same result with more formatting options.

Contents

• Using CAST Function
• Using CONVERT Function
• Using DATEFROMPARTS Function
• Using DATEADD and DATEDIFF Functions
• Using FORMAT Function
• Comparison of Methods
• Best Practices

Using CAST Function

The CAST() function is the simplest way to extract the date portion from a datetime value in SQL Server. When you cast a datetime value to a date type, SQL Server automatically removes the time component.

SELECT CAST(GETDATE() AS DATE) AS ExtractedDate;

This query will return the current date with the time set to 00:00:00.000. For example, if GETDATE() returns 2008-09-22 15:24:13.790, the result will be 2008-09-22.

Important Note: The DATE data type was introduced in SQL Server 2008. If you’re working with an older version of SQL Server, you’ll need to use alternative methods.

Using CONVERT Function

The CONVERT() function provides more flexibility and formatting options when extracting the date portion from a datetime value.

SELECT CONVERT(DATE, GETDATE()) AS ExtractedDate;

You can also use style codes with CONVERT to format the output differently:

-- Convert to date and then back to datetime with midnight time
SELECT CONVERT(DATETIME, CONVERT(DATE, GETDATE())) AS ExtractedDateTime;

This approach is particularly useful when you need to maintain the datetime data type while eliminating the time component.

Using DATEFROMPARTS Function

The DATEFROMPARTS() function allows you to construct a date value from individual year, month, and day components. While more verbose, it’s useful for date manipulation:

SELECT DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), DAY(GETDATE())) AS ExtractedDate;

This method is particularly valuable when you need to perform complex date operations or when working with date components separately before reconstructing them.

Using DATEADD and DATEDIFF Functions

You can also use a mathematical approach with DATEADD and DATEDIFF functions to strip the time component:

SELECT DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) AS ExtractedDate;

This works by:

1. Calculating the number of days since a base date (0)
2. Adding that many days back to the base date
3. This effectively removes the time portion

Using FORMAT Function

For SQL Server 2012 and later, you can use the FORMAT() function to extract the date portion:

SELECT FORMAT(GETDATE(), 'yyyy-MM-dd') AS ExtractedDate;

However, note that FORMAT() returns a varchar data type, not a date or datetime, which may require additional conversion if you need to perform date operations on the result.

Comparison of Methods

Best Practices

1. Choose the Right Data Type: Use the DATE data type when you only need date information, as it’s more storage-efficient and prevents time-related bugs.

2. Consider SQL Server Version: Be aware of version compatibility when choosing extraction methods.

3. Performance Considerations: CAST() and CONVERT() are generally the fastest methods for simple date extraction.

4. Time Zone Handling: Remember that datetime values in SQL Server don’t store timezone information. If you need to handle time zones, consider using DATETIME2 with time zone or application-level conversion.

5. Indexing: When filtering by date, ensure you’re using appropriate date functions that allow index usage. Avoid functions that prevent index seeks.

For your specific requirement of getting 2008-09-22 00:00:00.000 from 2008-09-22 15:24:13.790, the most efficient solution would be:

SELECT CONVERT(DATETIME, CAST(GETDATE() AS DATE)) AS ExtractedDateTime;

This converts the datetime to a date (removing time) and then back to a datetime type with midnight time, giving you exactly the format you requested.

Conclusion

Extracting the date portion from a SQL Server datetime value can be accomplished through several methods depending on your specific needs and SQL Server version. The CAST() and CONVERT() functions offer the most straightforward and performant solutions for modern SQL Server versions (2008 and later). For maintaining the datetime data type while setting time to midnight, using CONVERT(DATETIME, CAST(GETDATE() AS DATE)) provides the exact format you need. Always consider your SQL Server version, performance requirements, and data type compatibility when choosing the appropriate method for your use case.

1. Microsoft Docs - CAST and CONVERT
2. Microsoft Docs - Date and Time Data Types
3. SQL Server Helper - Date Functions