"SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())) "
Result: “2006-02-16 00:00:00.000”
Nice. Short and sweet…I so know I’ll need this and won’t remember where to find it…
I don’t play with T-SQL date and time formating often enough, so each time I do I seem to have to relearn the tricks and gimmicks. (Let’s not get me started on my feelings about date & time formatting in T-SQL, okay?)
If you’re interested in this, make sure you also check out the blog Anatoly references, Ayende Rahien's Blog [subscribed] and his related T-SQL DateTime post, JustDate: Striping the time part from datetime variables.