Cleaning a DateTime for the time portion/Converting DateTime.

by Allan Svelmøe Hansen January 05, 2009 19:07
As I’ve mentioned in other posts, a DateTime is stored using a numerical representation. This provides some interesting usages.
For example this means that in order to add days to a DateTime, one can simply add a number, like this: GetDate() + 5 which will add 5 days to the DateTime returned by GetDate(). Simple and easy.
You can however also convert a DateTime to a float, which provides some other “fun” possibilities.
If you convert a DateTime to a float, you’ll get a decimal number where the portion in front of the decimal point is the Date portion and the number after the decimal point is the Time portion (for 1900-01-01). For example:

DECLARE @DateTime DATETIME 
SET @DateTime 
'20081224 23:50:10' 
SELECT CAST(@DateTime AS float)  
  

This returns 39804.9931712963
If I then execute:

SELECT CAST(39804 AS DATETIME)

I get 2008-12-24 00:00:00.000 meaning the date representation of the DateTime. Subsequently doing it for the remainder portion (0.9931712963) I get 1900-01-01 23:50:10.000
This then means that to get a DateTime cleaned for the time portion all I need to do is:

SELECT CAST(FLOOR(CAST(@DateTime AS FLOAT)) AS DATETIME)    

Then I get the Date 2008-12-24 00:00:00.000.
While “fun”, SQL Server 2005 already has a built in conversion so the above will provide you with the same result as doing:

SELECT CONVERT(DATETIME,CONVERT(VARCHAR(10),@DateTime,105),105)
(the syntax can be read in the MSDN documentation)

I’ve found no difference in the performance between the two when running them, so my thoughts are that SQL Server optimizes them both to identical code to execute.
So which is preferred I guess is a matter of preference and personal opinion.
Generally – I like the “Float” method cause it seems really clean, but it is perhaps more difficult to read and thus maintain further down the line for yourself or others, if one does not know the workings of it.
Code can be too clever as well.

Bookmark and Share DotnetKicks dotnetshoutout

Comments

11/11/2009 3:27:03 PM #

Brilliant post, your blog looks awesome. The date time functions are pretty useful. Now, I'll be heading on!

The fourth kind United States

Add comment




  Country flag

biuquote
  • Comment
  • Preview
Loading



Powered by BlogEngine.NET 1.6.1.0
Theme by Mads Kristensen | Modified by Mooglegiant

About:
Allan Svelmøe Hansen

My real name is Allan Svelmøe Hansen.
I live in Denmark, where I work as a developer for hedal:kruse:brohus using SQL Server and the .NET framework since 2004.
My primary fields of expertise is back-end data integration, database design and optimization.


       View Allan Svelmøe Hansen's profile on LinkedIn     

Disclaimer

The opinions expressed herein are my own personal opinions and thoughts and does not represent my employers view in any way, nor are my results guaranteed for all situations.
Content is presented "as is", with no warranty.