-- Performance testing of datetime trimming
-- Written by Jornada Del Muerto aka A.G. http://www.lythum.lt
--
-- select cast(floor(cast(getDate() as float)) as datetime)
-- select DateAdd(dd, 0, DateDiff(dd, 0, getDate()))
-- select CAST(CONVERT(CHAR(10), getDate(), 25) AS DATETIME)

-- results
declare @method_floor int
declare @method_datediff int
declare @method_cast_char int
declare @best varchar(100)

-- Test declaration
declare @c bigint, @d bigint
declare @begin datetime, @end datetime, @fake datetime
-- constants
set @d = 1000000

print '---------------------------------------------------------------------------------------------------'
print '--- TESTS STARTING '+cast(@d as varchar(50)) +' of cycles'
print '---------------------------------------------------------------------------------------------------'

-- FLOOR
set @c = 0
set @begin = getDate()
WHILE(@c<@d)
 BEGIN
	set @fake = cast(floor(cast(getDate() as float)) as datetime)
	set @c = @c+1
END
set @end = getDate()
set @method_floor = DATEDIFF (ms ,@begin, @end)
print 'method: cast(floor(cast(getdate() as float)) as datetime): '+convert(varchar(50), @method_floor, 25) + ' /['+convert(varchar, @begin, 25)+']-['+convert(varchar, @end, 25)+']'

-- DATEDIFF
set @c = 0
set @begin = getDate()
WHILE(@c<@d)
 BEGIN
	set @fake = DateAdd(dd, 0, DateDiff(dd, 0, getdate()))
	set @c = @c+1
END
set @end = getDate()
set @method_datediff = DATEDIFF (ms ,@begin, @end)
print 'method: DateAdd(dd, 0, DateDiff(dd, 0, getdate())): '+convert(varchar(50), @method_datediff, 25) + ' /['+convert(varchar, @begin, 25)+']-['+convert(varchar, @end, 25)+']'

-- CHAR CAST
set @c = 0
set @begin = getDate()
WHILE(@c<@d)
 BEGIN
	set @fake = CAST(CONVERT(CHAR(10), getDate(), 25) AS DATETIME)
	set @c = @c+1
END
set @end = getDate()
set @method_cast_char = DATEDIFF (ms ,@begin, @end)
print 'method: CAST(CONVERT(CHAR(10), getDate(), 25) AS DATETIME): '+convert(varchar(50), @method_cast_char, 25) + ' /['+convert(varchar, @begin, 25)+']-['+convert(varchar, @end, 25)+']'
print '---------------------------------------------------------------------------------------------------'
print ' Time in MS, FLOOR='+cast(@method_floor as varchar(50))+
	'ms, Datediff='+cast(@method_datediff as varchar(50))+
	'ms, CAST CHAR='+cast(@method_cast_char as varchar(50))+'ms'
print '---------------------------------------------------------------------------------------------------'

create table ##results (ms int, name varchar(100))
Insert into ##results (ms,name) values (@method_floor, 'cast(floor(cast(getDate() as float)) as datetime)')
Insert into ##results (ms,name) values (@method_datediff, 'select DateAdd(dd, 0, DateDiff(dd, 0, getDate()))')
Insert into ##results (ms,name) values (@method_cast_char, 'CAST(CONVERT(CHAR(10), getDate(), 25) AS DATETIME)')
SELECT @best=name from ##results Order by ms desc
drop table ##results -- cleanup of temp shit :)

print '---------------------------------------------------------------------------------------------------'
print '-- Best result is: '+@best
print '---------------------------------------------------------------------------------------------------'

