How Can I Efficiently Extract the Date Part from a DateTime Value in SQL Server?

Extract date from SQL Server datetime value

Most SQL applications handle data that contains date and time components. However, in some cases we only need to extract the date part and discard the time information. This can be effectively achieved through various techniques in SQL Server.

The most efficient method

While several methods exist, the most efficient way to extract dates from datetime in SQL Server is to use a combination of the DATEADD() function and the DATEDIFF() function. This method includes the following steps:

1. Use DATEDIFF(DAY, 0, ) to calculate the number of days between 0 and the given datetime. This step effectively calculates the daily offset from the epoch (January 1, 1900).
2. Apply DATEADD() to the DAY field and use the result of step 1 to obtain a new datetime with its time component reset to 00:00:00.

For example:

SELECT DATEADD(DAY, DATEDIFF(DAY, 0, '2008-09-25 12:34:56'), 0)

Compared to other options such as converting using CAST(FLOOR(CAST(... AS FLOAT)) AS DATETIME) or CONVERT(DATETIME, CONVERT(VARCHAR(10), ..., 120), 120) , this method has been proven to be the fastest and most efficient method.

Benchmark

Performance testing on a dataset containing over 130 million rows of records with timestamps accurate to milliseconds showed the following results:

方法	CPU时间(ms)
纯SELECT	422
Floor-Cast	625
字符串转换	1953
DateAdd	531

It’s worth noting that performance may vary depending on the size, distribution, and other factors of your data.

The above is the detailed content of How Can I Efficiently Extract the Date Part from a DateTime Value in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!