SQL: Getting just the datepart from a datetime

A nice little trick:

If you have a datetime field which has a date and a time in it, and you ony need the time, you can use the following code to cast it to a date only value:

select cast(floor(cast(getdate() as float)) as datetime)

First the code casts the datetime into a float. Then it rounds off the float leaving just the date. Then it casts the float back to datetime.

This trick is useful for SQL 2005. SQL 2008 has a function for this issue.