TSQL: Truncating Datetime

It crops up all the time.  You have a datetime column.  In your select, you don't care about the time, you only care about the date because you want to group all records for a particular date, for example.  So what's the quickest way to go about it?

Well let's start with the options...

1. CONVERT
2. FLOOR
3. DATEADD/DATEDIFF

If you are using SQL 2008, you could add another option and CAST to DATE.  Perfect if the recipient of the data will accept it.  I'm gonna assume however, as is most likely at this point in time, that you aren't using SQL 2008 yet as that does seem to still be the case in most organisations.  I'm also going to assume that you need to return a datetime.

So, to option 1: CONVERT.

A popular choice because it's easily readable.  Let me give you an example.

CAST((CONVERT(varchar(11),getdate(),120)) as DATETIME)

This will return 2009-04-09 00:00:00.000

Option 2:  FLOOR

CAST(FLOOR(CAST(Getdate() as FLOAT)) as DATETIME)

Returns 2009-04-09 00:00:00.000

Obviously faster than convert because we aren't working with any strings.

Option 3: DATEADD

DATEADD(dd, DATEDIFF(dd, 0 getdate()), 0)

Returns 2009-04-09 00:00:00.000 (shock horror!)

Guess what?  This version is even faster again because we don't have any conversion to do at all.

To what degree are these options faster?

Well, a quick test by plonking a million records into a temp table which contains only a datetime column showed my execution times to be fairly similar but my CPU times  were increasingly quicker.

CONVERT - 1591

FLOOR - 434

DATEADD - 382

The convert is horrible obviously and should never be considered even at the expense of readability.  Clearly there's not a vast difference between FLOOR and DATEADD but I guess I like the DATEADD method for it's lack of conversion as well as it's performance gain, however small.  It just seems cleaner to me.