Just a week ago my Database Team member asked me what is the best way to only select date part from datetime. When ran following command it also provide the time along with date.
The required outcome was only 2007/06/10.
I asked him to come up with solution by using date functions. The method he suggested was to use
SELECT DATEADD(D, 0, DATEDIFF(D, 0, GETDATE()))
I approved his method though, I finally suggested my method using function CONVERT.
The reason I use this because it is very convenient as well as provides quick support to convert the date in any format. The table which suggest many format are displayed on MSDN.
Some claims that using CONVERT is slower then using DATE functions but it is extremely negligible. I prefer to use CONVERT.
Reference : Pinal Dave (http://blog.SQLAuthority.com)