Madhivanan,

Thank you for this. Some thoughts:

• I find mixing Cast() and Convert() in the same expression to be confusing. Would you switch them all to one or the other? I prefer Convert because it is easier for me to scan quickly. Also, would you mind putting the names of the methods in the comparison chart? It is hard to compare results when one has to jump up and down to try to find which method each numbered item refers to. The expressions are short enough that in fact I'd like to see the expressions themselves in the table rather than descriptions.

• When you do a large I/O operation such as inserting into a table, the duration values mostly represent I/O and not the part you are really interested in testing. IO times can vary wildly due to how space is allocated during the operation for writing to disk and how the log file is written. I think a better test would be: instead of creating a table of the result, do select @datetimevar = {expression} which will do the same amount of work, but not write the result anywhere. This will allow your durations to stop hiding the interesting part in a mountain of I/O.

• Dalibor's method 1 is not correct. Please try select dateadd(day, convert(int, Convert(datetime, '20120903 23:59:59.997') - 0.5), 0) which yields 2012-09-04 00:00:00.000. Instead, use SELECT DateAdd(day, Convert(int, GetDate() - '12:00:00.003'), 0). Or, try SELECT Convert(datetime, Convert(int, GetDate() - '12:00:00.003')) as even simpler. I do not like using 0.50000004 as that is a "magic number" that doesn't mean anything to anyone.

Everyone,

I would like to reiterate that casting datetime as float is an inherently unsafe operation. It works for the purposes of removing the time portion, but is a hack at best. I think we should NOT use it in our code, even though it works, because of the risk of communicating to less experienced developers that such conversions are accurate. Try SELECT Convert(datetime, Convert(float, Convert(datetime, '20120903 23:59:59.993'))) and see that it returns 2012-09-03 23:59:59.990.

It is my belief that code should be, in order:

1. Correct
2. Clear
3. Concise
4. Fast

The convert-to-float method loses out on #2 because in some situations it does not fulfill #1. A correct, clear, concise method that is slightly slower is to be preferred over an unclear method.

In my opinion, the winners of all these various time-removal queries that best satisfy the above are Convert(date) in SQL 2008+ and DateAdd(DateDiff()) in SQL 2005 and under. They also happen to be the fastest for their respective versions, but that is beside the point.

ErikEckhardt, Thanks for your valuable feedback. Points taken. I have modified the result table to include names and expressions.

Regarding your point "instead of creating a table of the result, do select @datetimevar = {expression} which will do the same amount of work, but not write the result anywhere", do you mean Instead of writing to #t, for each method I should do something like below

declare @datetime datetime

select 
    @datetime =dateadd(day,datediff(day,0,date_time),0) from
    (
    select 
    	top 1000000 
    		dateadd(day,checksum(newid())%10000,getdate()) as date_time
    from 
    	sys.objects as so1 cross join
    	sys.objects as so2 cross join
    	sys.objects as so3 cross join
    	sys.objects as so4
    ) as t


select 
    @datetime =cast(date_time as date) from
    (
    select 
    	top 1000000 
    		dateadd(day,checksum(newid())%10000,getdate()) as date_time
    from 
    	sys.objects as so1 cross join
    	sys.objects as so2 cross join
    	sys.objects as so3 cross join
    	sys.objects as so4
    ) as t

.
.
.
etc

Madhivanan That is exactly what I meant! It really does do the work of calculating the results, but doesn't have to output them to a table (or to the client, either way).

Thanks for updating the test results table. It is MUCH easier to compare now.

@Madhivanan thanks very much for this article; I've been using CONVERT(CHAR(10),dateTimeCol,120) for years without thinking too much. From the test I did myself based on your code it turns out that it's even slower than method 3. Many hundreds our views suffer from this. I think we'll be replacing it with method 1 or 2 if >= 2008.

@Madhivanan regarding Eriks suggestion to use "select @datetimevar = {expression}" I did

select 
    min(cast(datetime_col as date))
from    
    #t

what gave me only 1 row instead of 1 million. Note the MIN function.

chojrak11, Thanks for your test. You should note that the format that style 120 produces is ambiguous. You should always use unambiguous date format. Now run this and see what you get

set dateformat ydm

declare @datetimecol datetime
set @datetimecol ='20101219 19:12:11'
select  convert(datetime,convert(char(10),@datetimecol ,120))

You will get an error

Msg 242, Level 16, State 3, Line 6
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Also read this post http://beyondrelational.com/modules/2/blogs/70/posts/17523/always-use-unambiguous-date-formats-in-the-queries.aspx

@chojrak11,

Producing only one row is not correct. The server could do an optimization wherein it does an index seek to find the lowest value and then do the conversion operation only once (I'm speaking generally here, not specifically to this situation). If you want to do a real test of something, you assign it to a variable to "eat" the values, but still make the server do all the exact same work, without producing an actual rowset.

@Erik thanks, you're right of course. I have to get rid of some my bad habits :) I knew MIN/MAX etc. would have worked in this case (no index, no constraint), but for some less experienced SQL person it could mean that it works in any case - which can fail as you described. Have a good day.