Date only in sql

Question

My data is like below

2010-11-09 13:20:02.000

I'd like to get date only. Which sql function will i use?

Answer 1

My preference (because of its flexibility) is DATEADD/DATEDIFF:

SELECT DATEADD(day,DATEDIFF(day,0,'2010-11-09T13:20:02.000'),0)

It's flexible, because you can use the same pattern to achieve other rounding effects. E.g. to get the first of the month, you can dateadd/datediff month rather than day. It can also be used to find, e.g. the end of the month for the given date, or the end of the month, 3 months from the given date.