If you are using DATETIME, these are better ways to get today at 00:00:00.000. The nested DATEADD and DATEDIFF are a more efficient way to do this conversion than conversion to a character string and back again to DATETIME.

Select dateadd(day,datediff(day,0,current_timestamp),0) as CurrDate

Select dateadd(day,datediff(day,0,getdate()),0) as CurrDate

If you are using SQL 2008 or later, then the way Joe Celko suggested will work fine if you want a DATE column.

If you need a DATETIME, this also works in SQL 2008 or later:

select convert(datetime,convert(date,current_timestamp)) as CurrDate