Which method provides the best performance when removing the time portion from a datetime field in SQL Server?
a) select DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0)
or
b) select cast(convert(char(11), getdate(), 113) as datetime)
The second method does send a few more bytes either way but that might not be as important as the speed of the conversion.
Both also appear to be very fast, but there might be a difference in speed when dealing with hundreds-of-thousands or more rows?
Also, is it possible that there are even better methods to get rid of the time portion of a datetime in SQL?
Strictly, method a
is the least resource intensive:
a) select DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0)
Proven less CPU intensive for the same total duration a million rows by someone with way too much time on their hands: Most efficient way in SQL Server to get a date from date+time?
I saw a similar test elsewhere with similar results too.
I prefer the DATEADD/DATEDIFF because:
Edit, Oct 2011
For SQL Server 2008+, you can CAST to date
i.e. CAST(getdate() AS date)
. Or just use date
datatype so no time
to remove.
Edit, Jan 2012
A worked example of how flexible this is: Need to calculate by rounded time or date figure in sql server
Edit, May 2012
Do not use this in WHERE clauses and the like without thinking: adding a function or CAST to a column invalidates index usage. See number 2 here Common SQL Programming Mistakes
Now, this does have an example of later SQL Server optimiser versions managing CAST to date correctly, but generally it will be a bad idea ...
Edit, Sep 2018, for datetime2
DECLARE @datetime2value datetime2 = '02180912 11:45' --this is deliberately within datetime2, year 0218
DECLARE @datetime2epoch datetime2 = '19000101'
select DATEADD(dd, DATEDIFF(dd, @datetime2epoch, @datetime2value), @datetime2epoch)
@David Sopko for the Oct 2011 edit then code would be: select cast(GETDATE() as date)
For more recent versions of SQL, using date instead of datetime avoids the need to deal with hours. Use the following sample: declare noTime date = getdate(), withTime datetime = getdate() select @noTime,@withTime
the cast as date is great if you just need the date. However often you need the current date at midnight so you can then do some further date manipulation. the
DATE
data time is obnoxiously restrictive at what it will let you do with regard to things like dateadd, datediff and interacting with other date/time data types. For those cases, theDATEADD()
approach reigns king.This does not work for every date. I had mistakenly entered
0218
instead of2018
as the year and theDATEDIFF
part of your statement throws an exceptionThe conversion of a datetime2 data type to a datetime data type resulted in an out-of-range datetime value
Try:select DATEDIFF(dd, 0, convert(datetime2(0), '0218-09-12', 120))
@BernhardDöbler in Jul 2009 when I answered , "0218" would have been a valid date so you would not have got this far. Also the "0" does not convert to 19000101 for datetime2. Try this select
SELECT DATEDIFF(dd, '19000101', convert(datetime2(0), '0218-09-12', 120))