Removing the Time from SQL SERVER datetime

Just discovered a new trick, one that speeds up my SQL. I often use datetime columns and hate the SQL Server format where all dates include the time, which results in date conversion or bizarre arithmetics. Here ’s a nice solution


RETURN CAST( FLOOR( CAST( @d AS INTEGER) ) AS DATETIME )


What does it do? The cast/floor/cast trio pushes the internal datetime to a float, then removes the decimal part and converts it back to a datetime, as the time portion of the datetime datatype in SQL server is stored in the decimal places of a float equivalent type, then converting to an integer removes them and sets the date to midnight.