What's the quickest way to strip time from a datetime in SQL Server?

Data and Databases
1

I’m trying to find the most efficient method to remove the time part from a datetime value in SQL Server. Right now, I’m using this:

SELECT CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)

But I’m wondering if there’s a faster or more elegant solution. I need something that:

  1. Is super quick (fewer casts would be great)
  2. Gives me a datetime result, not a string

Anyone know a better trick? I’m dealing with a lot of data, so every millisecond counts. Thanks for any tips!

2

yo, try CONVERT(date, GETDATE()). it’s simple and fast. but if u need datetime, wrap it in CAST like this: CAST(CONVERT(date, GETDATE()) AS datetime). works like a charm for me. hope it helps!

3

have u tried using DATEADD? something like:

SELECT DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)

it might be faster cuz it avoids casting to float. curious if anyone’s benchmarked these methods? what kinda performance gains are u seeing with different approaches?

4

Another efficient approach is to use the DATETIME2FROMPARTS function. It allows you to construct a datetime value using only the date components:

SELECT DATETIME2FROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), DAY(GETDATE()), 0, 0, 0, 0, 0)

This method avoids multiple conversions and directly creates a datetime with zeroed-out time components. It’s particularly useful when working with large datasets where performance is critical. The function’s precision and lack of implicit conversions make it a reliable choice for stripping time from datetime values in SQL Server.