Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

DateTime Puzzle Expand / Collapse
Author
Message
Posted Saturday, May 5, 2012 5:20 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, September 18, 2013 6:40 AM
Points: 253, Visits: 79
Comments posted to this topic are about the item DateTime Puzzle
Post #1295598
Posted Saturday, May 5, 2012 5:21 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:50 PM
Points: 5,594, Visits: 24,997
Thanks, a nice easy question

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Post #1295599
Posted Saturday, May 5, 2012 7:40 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 4:20 PM
Points: 11,194, Visits: 11,142
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 col1
INTO #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 White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1295607
Posted Monday, May 7, 2012 12:28 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 11:26 AM
Points: 13,622, Visits: 10,514
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 LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1295792
Posted Monday, May 7, 2012 12:45 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 2:48 AM
Points: 5,977, Visits: 8,238
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. <evil grin>



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1295799
Posted Monday, May 7, 2012 1:14 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:05 AM
Points: 2,933, Visits: 2,955
The answer was easy.
I also appreciate the discussion from SQL Kiwi.

Thanks
IgorMi




Igor Micev,
SQL Server developer at Seavus
www.seavus.com
Post #1295811
Posted Monday, May 7, 2012 1:14 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:05 AM
Points: 2,933, Visits: 2,955
The answer was easy.
I also appreciate the discussion from SQL Kiwi.

Thanks
IgorMi




Igor Micev,
SQL Server developer at Seavus
www.seavus.com
Post #1295812
Posted Monday, May 7, 2012 1:14 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:05 AM
Points: 2,933, Visits: 2,955
The answer was easy.
I also appreciate the discussion from SQL Kiwi.

Thanks
IgorMi




Igor Micev,
SQL Server developer at Seavus
www.seavus.com
Post #1295813
Posted Monday, May 7, 2012 2:59 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 5:18 AM
Points: 3,925, Visits: 5,112
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


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 4:20 PM
Points: 11,194, Visits: 11,142
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 White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1295870
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse