Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Browse by Tags · View All
sql_server 217
t-sql 211
tsql 116
sqlserver 96
BRH 78
#SQLServer 66
#TSQL 56
SQL Server 34
function 11
SSMS 9

Archive · View All
August 2007 17
August 2010 8
June 2012 7
June 2011 7
November 2007 7
August 2012 6
May 2012 6
November 2011 6
August 2011 6
October 2011 6

Madhivanan's TSQL Blog

Different ways to remove TIME part from DATETIME values - Faster methods

Sep 3 2012 12:00AM by Madhivanan   

I have posted a blog post about Different ways to remove TIME part from DATETIME values where I have shown six different ways to remove TIME part. Some of the regular users posted some alternate methods. Here is the analysis of how long each method takes.

Create a temporary table named #t

create table #t(datetime_col datetime) 

 

Populate one million data into this table

insert into #t (datetime_col)
select 
	top 1000000 
		dateadd(day,checksum(newid())%10000,getdate())
from 
	sys.objects as so1 cross join
	sys.objects as so2 cross join
	sys.objects as so3 cross join
	sys.objects as so4
	

Test various methods to remove TIME part from DATETIME values

SET STATISTICS TIME ON 
print '======================================================'
Print 'Method 1 starts'
print '======================================================'
select 
	dateadd(day,datediff(day,0,datetime_col),0)			
from	
	#t
print '======================================================'
Print 'Method 2 starts'
print '======================================================'

select 
	cast(datetime_col as date) 							
from	
	#t
print '======================================================'
Print 'Method 3 starts'
print '======================================================'
select 
	cast(convert(char(8),datetime_col,112) as datetime)	
from	
	#t
print '======================================================'
Print 'Method 4 starts'
print '======================================================'
select 
	cast(cast(datetime_col as varchar(11)) as datetime)	
from	
	#t
print '======================================================'
Print 'Method 5 starts'
print '======================================================'
select 
	datetime_col-cast(cast(datetime_col as time) as datetime) 	
from	
	#t
print '======================================================'
Print 'Method 6 starts'
print '======================================================'
select 
	datetime_col-convert(char(10),datetime_col,108)				
from	
	#t
print '======================================================'
Print 'Method 7 starts'
print '======================================================'
select 
	cast(floor(cast(datetime_col as float)) as datetime) 
from 
	#t 
print '======================================================'
Print 'Method 8 starts'
print '======================================================'
select 
	cast(cast(cast(cast(datetime_col as binary(8)) as binary(4)) as binary(8)) as datetime) 
from 
	#t 
print '======================================================'
Print 'Method 9 starts'
print '======================================================'
select 
	datetime_col-cast(datetime_col as binary(4)) 
from 
	#t 
print '======================================================'
Print 'Method 10 starts'
print '======================================================'
select 
	dateadd(day,convert(int,datetime_col - 0.5),0) 
from 
	#t
print '======================================================'
Print 'Method 11 starts'
print '======================================================'
select 
	convert(datetime,format ( datetime_col, N'yyyy-MM-dd')) 
from 
	#t
print '======================================================'
Print 'Method 12 starts'
print '======================================================'
select 
	convert(datetime,convert(int,convert(float,datetime_col))) 
from 
	#t 
SET STATISTICS TIME OFF

Here is the output shown in tabular format

Method # Suggested by Expression CPU Time Elapsed Time
1 Madhivanan dateadd(day,datediff(day,0,datetime_col),0) 452 9328
2 Madhivanan cast(datetime_col as date) 359 9258
3 Madhivanan cast(convert(char(8),datetime_col,112) as datetime) 1201 7673
4 Madhivanan cast(cast(datetime_col as varchar(11)) as datetime) 4212 12061
5 Madhivanan datetime_col-cast(cast(datetime_col as time) as datetime) 421 9530
6 Madhivanan datetime_col-convert(char(10),datetime_col,108) 2028 10221
7 Yusuf Bhiwandiwala cast(floor(cast(datetime_col as float)) as datetime) 515 8660
8 Vladimir cast(cast(cast(cast(datetime_col as binary(8)) as binary(4)) as binary(8)) as datetime) 780 13229
9 Madhivanan datetime_col-cast(datetime_col as binary(4)) 702 10200
10 Dalibor dateadd(day,convert(int,datetime_col - 0.5),0) 640 7319
11 Dalibor convert(datetime,format ( datetime_col, N'yyyy-MM-dd')) 65832 90466
12 jerik1 convert(datetime,convert(int,convert(float,datetime_col))) 499 12370

 

Out of these methods Method 2 to convert DATETIME to DATE (from version 2008 onwards) gives better performance. However if you want to change again back to DATETIME, it may take slightly higher time.

Methods 1, 5,7 and 12 are all close to each other.  As always the methods that involve character conversions are slower.  The format method of version 2012 gives worst performance. So if you want to remove TIME part from DATETIME values, do not use the methods that involve in character conversions.

Tags: 


Madhivanan
3 · 40% · 12936
7
 
0
Lifesaver
 
 
 
0
Incorrect



Submit

8  Comments  

  • 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.

    commented on Sep 3 2012 1:29PM
    ErikEckhardt
    65 · 3% · 898
  • 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
    
    commented on Sep 4 2012 12:17AM
    Madhivanan
    3 · 40% · 12936
  • 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.

    commented on Sep 4 2012 3:16AM
    ErikEckhardt
    65 · 3% · 898
  • @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.

    commented on Sep 6 2012 2:52AM
    chojrak11
    1029 · 0% · 25
  • @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.

    commented on Sep 6 2012 2:57AM
    chojrak11
    1029 · 0% · 25
  • 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

    commented on Sep 6 2012 3:16AM
    Madhivanan
    3 · 40% · 12936
  • @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.

    commented on Sep 6 2012 6:13PM
    ErikEckhardt
    65 · 3% · 898
  • @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.

    commented on Sep 7 2012 2:04AM
    chojrak11
    1029 · 0% · 25

Your Comment


Sign Up or Login to post a comment.

"Different ways to remove TIME part from DATETIME values - Faster methods" rated 5 out of 5 by 7 readers
Different ways to remove TIME part from DATETIME values - Faster methods , 5.0 out of 5 based on 7 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]