Archive: Getting the Date portion of a DateTime type in SQL (T-SQL) – Use Floats and Floor! May 3, 2008Posted by reddogaw in Archive, SQL Server.
Tags: Convert, DateTime, SQL Server
Update – 2 May 2008 – Watch out for SQL Server 2008′s new Date and Time data types!
Something that comes up quite frequently when dealing with reporting and DateTime fields on a data table is grouping by the Date portion only of the field or filtering for a full day of data.
If you were to filter DateTime values where the actual time portion is used then doing:
SELECT * FROM Sales WHERE Sales.TransactionTime = '19 Jun 06'
Then you will only be getting the sales that were created at midnight!
Often you will see people wanting to only get the date portion by using a string conversion or a convoluted mix of SQL DateTime functions. For example:
SELECT * FROM Sales WHERE CONVERT(varchar(10), Sales.TransactionTime, 101) = '2006-06-19'
But this is somewhat slower and more memory intensive than a way in which two co-workers (Mehmet and Mark) have suggested (I don’t think it was ever posted by them, but I’m sure its on the net somewhere). They say that you should convert the DateTime field to a float and floor it in order to get the date portion. This is will work as when the DateTime is converted to a float the date portion can be found as the whole number representation of the float, the time is the fractional portion.
Convert date to a float: SELECT CONVERT( Float, GETDATE())
Floor (take only whole number value) the float value to get only the date portion: SELECT FLOOR( CONVERT( Float, GETDATE()))
Convert back to date time (if needed) for display purposes: SELECT CONVERT(DateTime, FLOOR( CONVERT( Float, GETDATE())))
There are tonnes of posts regarding this, so checkout a few of them.