Tuesday, June 10, 2008

how to remove time component from a datetime value

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))