Get date part from a datetime value – SQL

Quite often during data conversion processes, I am required to get only the date part from a datetime or a timestamp value. Here is a trick that does not involve CASTing and is quick to respond:

DATEADD(DAY, 0, DATEDIFF(DAY, 0, <ColumnName>))

Here is how it works: Whether smalldatetime or datetime, DATEDIFF(DAY, 0 returns the number of days since the base date, and DATEADD(DAY, 0 returns the integer as a date value.

Neat, isn’t it ?