jump to navigation

Archive: Getting the Date portion of a DateTime type in SQL (T-SQL) – Use Floats and Floor! May 3, 2008

Posted by reddogaw in Archive, SQL Server.
Tags: , ,
trackback

Update – 2 May 2008 – Watch out for SQL Server 2008′s new Date and Time data types!

From: http://blogs.ssw.com.au/andrewweaver/archive/2006/06/19/8262.aspx

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.

For example:

  1. Convert date to a float: SELECT CONVERT( Float, GETDATE())

  2. Floor (take only whole number value) the float value to get only the date portion: SELECT FLOOR( CONVERT( Float, GETDATE()))

  3. 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.

About these ads

Comments»

1. Benjamin Marty - April 10, 2011

Why not just use Convert(Date, GETDATE())?

reddogaw - September 16, 2011

@Benjamin – this was before the days of Date type introduced in SQL Server 2008.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: