Best approach to remove time part of datetime in SQL Server
Solution 1
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:
- varchar is subject to language/dateformat issues
Example: Why is my CASE expression non-deterministic? - float relies on internal storage
- it extends to work out first day of month, tomorrow, etc by changing "0" base
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)
Solution 2
In SQL Server 2008, you can use:
CONVERT(DATE, getdate(), 101)
Solution 3
Of-course this is an old thread but to make it complete.
From SQL 2008 you can use DATE datatype so you can simply do:
SELECT CONVERT(DATE,GETDATE())
Solution 4
In SQL Server 2008, there is a DATE datetype (also a TIME datatype).
CAST(GetDate() as DATE)
or
declare @Dt as DATE = GetDate()
Solution 5
SELECT CAST(FLOOR(CAST(getdate() AS FLOAT)) AS DATETIME)
...is not a good solution, per the comments below.
I would delete this answer, but I'll leave it here as a counter-example since I think the commenters' explanation of why it's not a good idea is still useful.
Stephen Perelson
Seeking joy in everything. Finding contentment. Enjoying learning new tricks. Gaining satisfaction in helping others.
Updated on July 17, 2022Comments
-
Stephen Perelson almost 2 years
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?
-
MatBailie almost 15 yearsSee GBN's answer, many have investigated this. DATETIMEs are NOT stored as floats, and so using DATEADD/DATEDIFF avoids the mathmatical manipulation need to CAST between types.
-
Stephen Perelson almost 15 yearsThanks, I figured this had to have been asked before. Strange though that my experiments pointed out that the float method is actually slower by 3.5% on SQL Server 2008 than the dateadd(dd,0, datediff(dd,0, getDate())) method. I did run my tests many times for each method and the database server was unused for anything else at the time.
-
Gary McGill almost 15 yearsI can accept that you might want to avoid a cast from DATETIME to FLOAT for the reason you describe, but in that case isn't the implicit conversion from zero in the OPs option (a) also a problem? Hmmm... I suppose in that case it's not a FLOAT and that the server is probably smart enough to discard the time info. OK, I concede :-)
-
Philip Kelley almost 15 yearsI once devised a trigger to scrub times from selected columns. If the data can't be bad, you don't have to clean it.
-
Joel Coehoorn almost 15 yearsLet's just say that I'm skeptical of benchmarks done by anyone who hasn't demonstrated that they do benchmarks regularly and in a very scientific way as part of their job. Even Thomas' benchmark in the link by gbn has some obvious problems when you look at it. That doesn't make it wrong necessarily, just not definitive. The cast/floor/cast method was the accepted fastest way for a very long time, and I suspect it was once indisputably true. That said, I am starting to reconsider it; especially for sql server 2008, where it's completely unnecessary anyway.
-
MatBailie almost 15 yearsThe 0 is indeed an implicit conversion from a numeric type (INT I would guess) to a DATETIME. Because it's a constant expression, however, the optimiser can do that at compile time for Stored Procedures and only needs to do it once for dynamically execute SQL. In short, there is a one time overhead for that, the FLOAT based query has the equivilent overhead for every Row.
-
MatBailie almost 15 yearsThere is a downside to the UDF approach, they're not SARGable. If used in JOINs or WHERE clauses, the optimiser can't use INDEXes to improve performance. Using the DATEADD/DATEDIFF approach, however, is SARGable and will be able to benefit from INDEXes. (Apparently the FLOAT method is SARGable too)
-
Ben over 12 yearsThe string method is extremely easy to use, to read, and to remember. Those are very important factors which I think you are underestimating!
-
Joel Coehoorn over 12 years@Ben - easier to read than "CAST( x as Date)" ? The string method is also wrong because it doesn't always work. Deploy your database to a server with a different collation, and you're in big trouble.
-
Ben over 12 years@JoelCoehoorn, convert style 121 is called "ODBC Canonical". It does not vary with collation or locale. The string trick is also easy to generalise to year, year+month, day, hour or minute.
-
usr about 12 yearsCasting to float is terribly unprecise. This answer should be deleted. Nobody should use this code.
-
usr about 12 yearsThat is horrible. I'd never put my data at risk like this. Who knows if this is correct for all datetimes, not just the ones you tested.
-
Andriy M over 11 yearsEssentially same as @Gary McGill's suggestion.
-
Andriy M almost 11 yearsA valid option, yes. Suggested more than once in this thread, though.
-
Andriy M over 10 yearsThe third argument has absolutely no bearing on the result when converting from a
datetime
to adate
, and so your solution effectively boils down to justCONVERT(DATE,getdate())
, which has already been suggested more than once. -
ErikE over 10 yearsNot to mention that it's not safe to cast to float and back to datetime--float doesn't have enough precision. Therefore I think it can't be recommended at all. See this post for more detail.
-
ErikE over 10 years@Ben The string trick teaches developers to use string conversions. They work, but date math is far, far superior, for many reasons, not the least of which is speed--but even more, for what learning to work with the dates-as-numbers confers on the developer and his mental abilities to be fluid with number manipulation in code.
-
ErikE over 10 years@usr Oh, it's correct, it's just a magic number and shouldn't be used for that reason. If you want to check its correctness, just stuff all the possible dates for one day in a table and check the results! Also see this post for more information.
-
ErikE over 10 yearsCasting as float is not safe.
-
ErikE over 10 yearsCasting as float is not safe.
-
ErikE over 10 yearsUsing a UDF can be good in some situations (like when scrubbing parameters). But in most situations it is an awful solution--running a UDF once for each row is a way to just kill the performance of a query, without any need for it!
-
ErikE over 10 yearsConverting to float is not safe.
-
ErikE over 10 years@MatBailie I beg to differ! UDFs are definitely not SARGable, but neither is Dateadd nor is Convert to float!
WHERE DateAdd(DateDiff(Column)) = @DateValue
won't use an index. On the other hand,WHERE Column >= dbo.UDF(@DateValue) AND Column < dbo.UDF(@DateValue + 1)
is SARGable. So be careful how you put it. -
ErikE over 10 yearsConverting to float is not safe.
-
DMpal Jain over 10 years@ErikE - I don't disagree, Erik, UDF's are performance killers which is why I say that, if you can use SQL Server 2008 or above and use a built-in datatype that does this for you, that will be the best solution (both in terms of achieving what's required and in terms of performance). If you're stuck with an older version of SQL Server that doesn't natively support this, you're going to give up something in order to achieve your requirements.
-
usr over 10 years@ErikE good point. Your answer provides the possibility of using
'12:00:00.003'
which I think is much better, though. -
ErikE over 10 yearsTrue. It would be nice if the database engine gave us something that was SARGable, but easier to express. In the meantime, if you're looking for a value that's any time during a whole day, this is still the best solution (for at least older versions of SQL):
WHERE DateColumn >= {TimeTruncatingExpression}(@DateValue) AND DateColumn < {TimeTruncatingExpression}(@DateValue + 1)
. I felt like I had to say something since you said "I almost always use UDFs" didn't explain any of the drawbacks, nor the way to make a date-only query SARGable. -
DMpal Jain over 10 years@ErikE - No worries, Erik. When I've used UDF's, I've either been working with small data sets where performance isn't paramount, or more likely I've been filtering the query against the "raw" date field (to ensure sargability) but selecting the column with the UDF applied. As these are usually small datasets once filtered, running the UDF over this small number of records isn't such a performance hit. That said, you do raise a very good point and I've updated my answer to reflect this.
-
Andriy M over 10 yearsWhy would you do that? Do you think that extracting bits from a
datetime
value, converting them to strings, concatenating those together and finally converting the result back todatetime
is better than e.g. performing direct calculations on the originaldatetime
(theDATEADD
/DATEDIFF
method)? -
Andriy M over 10 yearsAlso, what are
MM
andDD
? There are no such functions in SQL Server. -
Adam Wenger over 10 years.999 cannot be stored in SQL Server in a
DATETIME
column. The highest available is .997 From: msdn.microsoft.com/en-us/library/ms187819.aspx you'll see that the values are rounded to have the thousandth place to 0, 3, or 7. The OP will not see the value from your test in their tables. -
broslav over 10 yearsYou are correct. I didn't mean to post this as an answer to the OP question, but as a comment for others to see, but I only had 11 reputation points and 15 is needed for commenting.
-
Andriy M over 10 yearsIn your first snippet the string constant is implicitly converted to a datetime, in your second one it remains a string (and the 113 is just ignored).
-
Mikael Eriksson over 10 yearsAlread suggested in these answers: stackoverflow.com/a/17449578/569436 stackoverflow.com/a/10451347/569436 stackoverflow.com/a/19541838/569436 stackoverflow.com/a/1177529/569436
-
Andriy M over 10 yearsWhat is the principal difference of your suggestion from the method mentioned in @broslav's answer or from the method that was determined as slowest in this thread (same link as in the accepted answer)?
-
Dr. Andrew over 10 yearsThey are not the same. The other answers suggested casting it to a date with no time component and leave it like that. My posting sets it to a datetime with the time at midnight. There is a big difference; try exporting to MS Excel and you'll see that it handles the datetime much better than date.
-
Mikael Eriksson over 10 yearsThe first one is exactly the same.
-
Dr. Andrew over 10 yearsOk, yes, I do see that one now. I will be happy to remove my answer as a duplicate, if necessary.
-
EWit almost 10 yearsThis is better fit as an edit on the accepted answer. With 20 other answers this will be buried and nigh unfindable. Also the accepted answer makes mention of using
cast
: For SQL Server 2008+, you can CAST to date. Or just use date so no time to remove. -
Andriy M almost 10 yearsIt would be best to post this as an answer to an equivalent Sybase question. If there is no such question, you are free to create one (and answer it yourself).
-
Andriy M almost 10 yearsBesides, it is pointless to specify a third parameter to CONVERT when you are converting a
datetime
todate
: neither of those has an inherent format. -
Choco Smith over 9 years@David Sopko for the Oct 2011 edit then code would be: select cast(GETDATE() as date)
-
Max Vargas almost 9 yearsThanks Andriy! I didn't know my recommendation wasn't that efficient. At least it works, but you are right.
-
ozkary over 7 yearsFor 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
-
Xedni almost 7 yearsthe 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. -
joelmdev over 6 yearsThis is what I used and it worked well. Seems like the simplest answer. Any downsides over using in conjunction w/ CONVERT?
-
troy almost 6 yearsCAST and CONVERT are equivalent in function. The difference is that CAST is part of the ANSI standard, while CONVERT is specific to T-SQL. So, use CAST wherever possible.
-
Admin almost 6 yearsJust use
CAST(GETDATE() AS DATE)
or strictly ANSICAST(CURRENT_TIMESTAMP AS DATE)
which I think is worthless. Stay with the first one. -
Admin almost 6 years@troy I use CAST because I can save 3 typing letters and syntax is clearer than CONVERT, the ANSI Standard part is worthless
-
Bernhard Döbler almost 6 yearsThis 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))
-
ZygD almost 6 years@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))
-
Rudey almost 4 yearsNaming the variable
epoch
is confusing because it's not 1970, but 1900. 1900 is the minimum value thatDATEDIFF
uses, so it makes sense to keep using 1900. The variable should be renamed to something else. -
ZygD almost 4 yearsWhere is 1970 mentioned? @Rudey