Performance-Optimized Methods for Removing Time Part from DateTime in SQL Server

Introduction

In SQL Server database development, handling datetime data is a common requirement. The datetime data type contains both date and time information, but many business scenarios require only the date portion while removing the time component. Based on high-scoring Stack Overflow answers and discussions from multiple technical communities, this paper systematically analyzes the performance characteristics and applicable scenarios of different methods.

Comparative Analysis of Main Methods

According to extensive testing results validated on Stack Overflow, the DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0) method has been proven to be the optimal solution. This approach calculates the difference in days between the current date and a base date (1900-01-01), then adds the corresponding number of days to obtain a pure date value.

-- Optimal performance method example
DECLARE @current_date datetime = GETDATE()
SELECT DATEADD(day, DATEDIFF(day, 0, @current_date), 0) AS date_only

The main advantages of this method include:

• Lowest CPU resource consumption, especially excellent performance when processing hundreds of thousands of rows
• No string conversion involved, avoiding language and date format compatibility issues
• Strong extensibility, capable of calculating dates like month start or tomorrow by modifying base values

Analysis of Other Common Methods

CAST(convert(char(11), getdate(), 113) as datetime) is another common approach, but testing shows its performance is slightly inferior to the DATEADD/DATEDIFF combination. This method involves string conversion, generating additional overhead during large-scale data processing.

-- String conversion method example
SELECT CAST(CONVERT(char(11), GETDATE(), 113) AS datetime) AS date_only

For SQL Server 2008 and later versions, directly using CAST to the date type is the most concise solution:

-- Recommended method for SQL Server 2008+
SELECT CAST(GETDATE() AS date) AS date_only

Performance Testing Data

According to million-row testing conducted by the Stack Overflow community, the DATEADD/DATEDIFF method significantly outperforms other approaches in CPU usage. In tests with 3,000 rows of data, the floating-point conversion-based method executed in approximately 16ms, while the string concatenation-based method averaged 115ms, showing substantial performance differences.

Handling datetime2 Data Type

For the datetime2 data type, specific base dates are required:

DECLARE @datetime2value datetime2 = '02180912 11:45'
DECLARE @datetime2epoch datetime2 = '19000101'
SELECT DATEADD(dd, DATEDIFF(dd, @datetime2epoch, @datetime2value), @datetime2epoch)

Index Usage Considerations

Special attention is needed when using date functions in WHERE clauses. Applying functions or CAST operations to columns may invalidate index usage. Although newer versions of SQL Server optimizers have improved handling of CAST to date types, cautious usage in production environments is still recommended.

CONVERT Function Format Options

The CONVERT function supports multiple date formats, with commonly used style codes including:

-- Date conversion in different formats
SELECT CONVERT(varchar(10), GETDATE(), 101)  -- MM/DD/YYYY
SELECT CONVERT(varchar(10), GETDATE(), 102)  -- YYYY.MM.DD
SELECT CONVERT(varchar(10), GETDATE(), 103)  -- DD/MM/YYYY
SELECT CONVERT(varchar(10), GETDATE(), 111)  -- YYYY/MM/DD

Best Practice Recommendations

Based on performance testing and practical application experience, we recommend:

• Prioritize CAST to date type in SQL Server 2008+ environments
• For older versions, the DATEADD/DATEDIFF combination is the best choice
• When designing new tables, directly use the date data type to avoid time processing issues
• Avoid using date functions on indexed columns in WHERE clauses when possible

Conclusion

Through systematic analysis and performance testing, the DATEADD/DATEDIFF method demonstrates optimal performance characteristics in removing the time portion from datetime values. With SQL Server version updates, CAST to date type provides more concise syntax. Developers should choose appropriate methods based on specific SQL Server versions and performance requirements, while paying attention to index usage optimization strategies.