Get date without time in Sql Server

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:
 

150871
 
186319
 
94119

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

One Comment

  • gbn
    Posted 13 December, 2010 at 11:49 pm | Permalink

    Where is the DATEADD/DATEDIFF comparison?