There are times when you need to write a query joining on dates, that is the day of a date, but not worry about the time portion. Without using the between x and y, as sometimes this isn't possible from places like SSAS Data source views, what's the most efficient way to remove the time part of a datetime?
There are a number of ways to drop off the time part, as shown here:
select CONVERT(datetime, CONVERT(varchar(10), c.date, 111), 111) from dbo.phonecalls c
go
select cast(CAST(YEAR(c.date) AS VARCHAR(4)) + '/' +
CAST(MONTH(c.date) AS VARCHAR(2)) + '/' +
CAST(DAY(c.date) AS VARCHAR(2)) as datetime) from dbo.phonecalls c
go
select CAST(FLOOR(CAST(c.date AS DECIMAL(12, 5))) AS DATETIME) from dbo.phonecalls c
After clearing the query cache for the server (dbcc freeproccache) I executed the above in sequence, and got the following execution times:
So it appears that casting the date to a 5 point decimal, dropping the decimal portion, and then casting back to a date time is by far the most efficient. I'm putting this down the fact that it's a completely numerical operation, and doesn't resort to casting to different value types (ie: varchar).
posted @ Monday, March 31, 2008 11:45 AM