DateTime Puzzle

Question

Post reply

DateTime Puzzle

Sunil Chandurkar

Right there with Babe

Points: 791
More actions
May 5, 2012 at 5:20 pm

#261041

Comments posted to this topic are about the item DateTime Puzzle
May 7, 2012 at 2:59 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 24 total)

You must be logged in to reply to this topic. Login to reply

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

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[/url]
Before posting a performance problem please read[/url]

Paul White SSC Guru Points: 150468 More actions · Answer 2

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
All articles available on SQL.kiwi
@SQL_Kiwi

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 3

Thanks for the question. Easy one to start the week.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 4

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/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 5

The answer was easy.

I also appreciate the discussion from SQL Kiwi.

Thanks

IgorMi

Igor Micev,My blog: www.igormicev.com

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 6

The answer was easy.

I also appreciate the discussion from SQL Kiwi.

Thanks

IgorMi

Igor Micev,My blog: www.igormicev.com

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 7

The answer was easy.

I also appreciate the discussion from SQL Kiwi.

Thanks

IgorMi

Igor Micev,My blog: www.igormicev.com

Paul White SSC Guru Points: 150468 More actions · Answer 8

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
All articles available on SQL.kiwi
@SQL_Kiwi

Thomas Abraham SSChampion Points: 10761 More actions · Answer 9

SQL Kiwi (5/5/2012)
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.

Thanks for the illuminating post. The "SELECT CONVERT(binary(8), @dt);" was most interesting, as I had never thought of explicitly showing the representation of date values that way.

Thanks to OP for the question. A nice easy one for Monday.

[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn

Nils Gustav Stråbø SSChampion Points: 11259 More actions · Answer 10

SQL Kiwi (5/5/2012)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:

Which would lead me to believe that each "tick" in the time integer is actually 3 1/3 ms and not 3.3 ms, which makes sense regarding the rounding.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 11

SQL Kiwi (5/5/2012)
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.

+1

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Revenant SSC-Forever Points: 42467 More actions · Answer 12

Revenant

SSC-Forever

Points: 42467

May 7, 2012 at 8:35 am

#1484080

A 5-second question... Thanks!

Lon-860191 SSCrazy Points: 2449 More actions · Answer 13

Lon-860191

SSCrazy

Points: 2449

May 7, 2012 at 8:45 am

#1484085

Thanks, an easy way to start this week.