Hi
I needed to truncate the time part from a datetime column in a query I perform, in order to group by the date part of my datetime column, a pretty standard requirement. I have found 3 methods to do so over the web, and I wondered which one performs best since I had to apply it to millions of records.
I have conducted a benchmark in which i tried to measure the time it takes SQL Server to perform the truncation. Since I wanted to isolate it as much as possible, I have performed a query that selects the maximum date from a table with 1 million rows. Here are the results:
No Casting - 373 milliseconds
Casting using convert to varchar - 1346 milliseconds
Casting using convert to float - 623 milliseconds
Casting using dateadd function - 490 milliseconds
It seems like we have a winner...
This is the script I used:
create table testdate (date_col datetime )
declare @i int
set nocount on
set @i = 0
begin tran
while (@i < 1000000)
begin
insert into testdate values (getdate() + @i)
if ((@i % 5000)=0)
begin
commit
begin tran
end
set @i = @i+1
end
commit
declare @a datetime
declare @b datetime
set @a = getdate();
with a as
(
select date_col
from testdate
)
select max(date_col)
from a
set @b = getdate()
select 'No cast',datediff(ms,@a,@b)
set @a = getdate();
with a as
(
select CONVERT(datetime, CONVERT(varchar, date_col, 101)) date_col
from testdate
)
select max(date_col)
from a
set @b = getdate()
select 'Cast Using convert',datediff(ms,@a,@b)
set @a = getdate();
with a as
(
select cast(floor(cast(date_col as float)) as datetime) date_col
from testdate
)
select max(date_col)
from a
set @b = getdate()
select 'Cast using float',datediff(ms,@a,@b)
set @a = getdate();
with a as
(
select DATEADD(dd, DATEDIFF(dd,0,date_col), 0) date_col
from testdate
)
select max(date_col)
from a
set @b = getdate()
select 'Cast using dateadd',datediff(ms,@a,@b)
BTW, if you try casting the datetime value to int, for example cast(cast(date_col as int) as datetime)
you will find out a weird behaviour.
If the time part of the datetime value is before noon (12 AM), the casting will return the date part of the datetime value. If the time part is after noon, the casting will return the date of the next day, for example (actually it not exactly noon, but a bit before that)
select cast(cast(cast('2009-03-19 11:59:59.994' as datetime) as int) as datetime) =>2009-03-19 00:00:00.000
select cast(cast(cast('2009-03-19 11:59:59.995' as datetime) as int) as datetime)=>2009-03-20 00:00:00.000
Personally, I consider this as a bug since casting any non-whole number to int just truncates the number, for example - select cast(5.9 as int) returns 5
So mind this issue when you are dealing with datetime values.
Till next time...
March 19, 2009
Subscribe to:
Post Comments (Atom)
Hi Momi,
ReplyDeleteIf you have time, you should create a profile on www.sqlservernation.com and blog there too!
Tim Chapman (chapmandew)
Have you tried
ReplyDeleteselect CAST(getdate() as DATE)