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
CAST(MONTH(c.date) AS VARCHAR(2)) + ‘/’ +
CAST(DAY(c.date) AS VARCHAR(2)) as datetime) from dbo.phonecalls c
select CAST(FLOOR(CAST(c.date AS DECIMAL(12, 5))) AS DATETIME) from dbo.phonecalls c
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).