T-SQL to trim a datetime to the nearest date?

Question

Duplicate of What's the BEST way to remove the time portion of a datetime value (SQL Server)?

I have a column that tracks when things are created using a datetime, but I'd like to generate a report that groups them by day, so I need a way of nulling out the time component of a datetime column.

How do I do this?

Answer 1

Why not convert straight to date:

select convert(date, getdate())

This truncates days, not rounds. To round Days do this:

select convert(date, getdate() + 0.5)

Answer 2

One way is to change getdate() to your column name,

select dateadd(dd, datediff(dd, 0, getdate())+0, 0)

Answer 3

Here's another solution:

SELECT CAST( FLOOR( CAST( GETDATE() AS float) ) AS smalldatetime)