Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 DateTime Puzzle Rate Topic Display Mode Topic Options
Author
 Message
 Posted Saturday, May 5, 2012 5:20 PM
 SSC Veteran Group: General Forum Members Last Login: Wednesday, September 18, 2013 6:40 AM Points: 253, Visits: 79
Post #1295598
 Posted Saturday, May 5, 2012 5:21 PM
 SSCertifiable Group: General Forum Members Last Login: Sunday, January 4, 2015 7:55 AM Points: 5,333, Visits: 25,280
Post #1295599
 Posted Saturday, May 5, 2012 7:40 PM
 SSCrazy Eights Group: General Forum Members Last Login: 2 days ago @ 6:42 AM Points: 9,932, Visits: 11,347
 The explanation could be better. The question does not involve a float value. By default, the literal value 0.25 is interpreted as a numeric(2,2):`SELECT 0.25 AS col1INTO #v;EXECUTE tempdb.sys.sp_columns @table_name = N'#v', @table_owner = N'banana', @table_qualifier = N'tempdb', @column_name = N'col1';DROP TABLE #v;`If the intention has been to show a float, either a float literal or a typed variable could have been used:`SELECT CAST(25e-2 AS datetime);DECLARE @f float = 25e-2;SELECT CAST(@f AS datetime);`Datetime values are no more "associated" with a float value than they are with any other type that can be implicitly converted (see the conversion table in Books Online - CAST and CONVERT (Transact-SQL)). The internal representation is two integers - one for the number of days from the base date, and one for the number of ticks (1/300th second) since midnight. In fact the second format seems to be 0.003 second time intervals, rounded to 0, 3, or 7 in the third decimal place when used:`DECLARE @dt datetime = '1900-01-02 00:00:00.006';SELECT @dt;SELECT CONVERT(binary(8), @dt);`My biggest concern with this question though, is that it encourages people to be sloppy with types and relies on hidden implicit conversions. As a general rule, try to be explicit about types in T-SQL code. Paul WhiteSQLPerformance.comSQLblog.com@SQL_Kiwi
Post #1295607
 Posted Monday, May 7, 2012 12:28 AM
 SSCoach Group: General Forum Members Last Login: Today @ 1:35 AM Points: 15,510, Visits: 13,170
 Thanks for the question. Easy one to start the week. How to post forum questions.Need an answer? No, you need a question.What’s the deal with Excel & SSIS?Member of LinkedIn. My blog at SQLKover. MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1295792
 Posted Monday, May 7, 2012 12:45 AM
 SSCertifiable Group: General Forum Members Last Login: 2 days ago @ 1:48 PM Points: 7,765, Visits: 11,376
 Thanks, Paul! You saved me a lot of time by addressing all the points I wanted to address after reading the explanation of this question.It never ceases to amaze me how many people think datetime data is internally stored as a float. That statement is even being made explicitly in the topic referenced in the explanation of this question. And while this question uses the weaker verb "associate", it will again reinforce that believe for some people.And now, I am silently hoping that we'll get the same question tomorrow, with the same answer options - but with data type datetime2. Hugo Kornelis, SQL Server MVPVisit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1295799
 Posted Monday, May 7, 2012 1:14 AM
 SSCarpal Tunnel Group: General Forum Members Last Login: Today @ 12:49 PM Points: 4,012, Visits: 4,732
 The answer was easy. I also appreciate the discussion from SQL Kiwi.ThanksIgorMi Igor Micev,SQL Server developer at Seavuswww.seavus.com
Post #1295811
 Posted Monday, May 7, 2012 1:14 AM
 SSCarpal Tunnel Group: General Forum Members Last Login: Today @ 12:49 PM Points: 4,012, Visits: 4,732
 The answer was easy. I also appreciate the discussion from SQL Kiwi.ThanksIgorMi Igor Micev,SQL Server developer at Seavuswww.seavus.com
Post #1295812
 Posted Monday, May 7, 2012 1:14 AM
 SSCarpal Tunnel Group: General Forum Members Last Login: Today @ 12:49 PM Points: 4,012, Visits: 4,732
 The answer was easy. I also appreciate the discussion from SQL Kiwi.ThanksIgorMi Igor Micev,SQL Server developer at Seavuswww.seavus.com
Post #1295813
 Posted Monday, May 7, 2012 2:59 AM
 SSCertifiable Group: General Forum Members Last Login: Today @ 12:52 AM Points: 5,364, Visits: 7,073
 Thanks for the simple question.However, as stated by Paul, the idea that DATETIME et al are stored as FLOAT is highly disturbing. ____________________________________________Space, the final frontier? not any more...All limits henceforth are self-imposed.“libera tute vulgaris ex”
Post #1295840
 Posted Monday, May 7, 2012 4:55 AM
 SSCrazy Eights Group: General Forum Members Last Login: 2 days ago @ 6:42 AM Points: 9,932, Visits: 11,347
 Hugo Kornelis (5/7/2012)Thanks, Paul! You saved me a lot of time by addressing all the points I wanted to address after reading the explanation of this question.Makes a change! I normally get to the question after you and end up posting the '+1' I too, look forward to the repeat question based on DATETIME2. Paul WhiteSQLPerformance.comSQLblog.com@SQL_Kiwi
Post #1295870

 Permissions