What if I want to remove the time component from a datetime value ?
That is to say I want 2008-04-18 00:00:00.000 intead of 2008-04-18 13:46:57.983
Now since values with the datetime data type are stored internally by the SQL Server 2005 Database Engine as two 4-byte integers. The first 4 bytes store the number of days before or after the base date: January 1, 1900. The base date is the system reference date. The other 4 bytes store the time of day represented as the number of 1/300-second units after midnight.
reference : http://msdn2.microsoft.com/en-us/library/ms187819.aspx
so the simplest way of doing this I found is :
SELECT CONVERT(DATETIME,CONVERT(INT,datetimevalue))