Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


DateTime Puzzle


DateTime Puzzle

Author
Message
Thomas Abraham
Thomas Abraham
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2333 Visits: 2254
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.

Please don't go. The drones need you. They look up to you.
Connect to me on LinkedIn
Nils Gustav Stråbø
Nils Gustav Stråbø
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2013 Visits: 3575
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
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21089 Visits: 18259
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

Revenant
Revenant
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5801 Visits: 4718
A 5-second question... Thanks!
Lon-860191
Lon-860191
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1887 Visits: 278
Thanks, an easy way to start this week.
EL Jerry
EL Jerry
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3916 Visits: 1337
Nice easy question to begin the week.

"El" Jerry.

"El" Jerry.

"A watt of Ottawa" - Gerardo Galvan

To better understand your help request, please follow these best practices.
Paul White
Paul White
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10338 Visits: 11350
Nils Gustav Stråbø (5/7/2012)
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.

Thanks! Of course that's right - not sure how I managed to confuse myself before. Must be age catching up with me :-)



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Toreador
Toreador
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2417 Visits: 8064
To those who said the answer was easy - is this because you know how SQL handles implicit conversions of this sort, or because you ran the code? I didn't think it was easy at all, because I try not to run the code before answering, and because I would never dream of trying to cast non-date values in this way!
Andrew Watson-478275
Andrew Watson-478275
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1659 Visits: 2654
Toreador (5/8/2012)
To those who said the answer was easy - is this because you know how SQL handles implicit conversions of this sort, or because you ran the code? I didn't think it was easy at all, because I try not to run the code before answering, and because I would never dream of trying to cast non-date values in this way!


It's one of those cases where it's really easy - if you know the answer already ;-). I can't see that you would be able to work this out from first priciples without doing some reading. I'd say all of the people who've said it's easy know about these implicit conversions.

As you (and quite a number of the earlier respondents) say, it's not something that you should really be using without a very good reason.
marlon.seton
marlon.seton
SSC Eights!
SSC Eights! (845 reputation)SSC Eights! (845 reputation)SSC Eights! (845 reputation)SSC Eights! (845 reputation)SSC Eights! (845 reputation)SSC Eights! (845 reputation)SSC Eights! (845 reputation)SSC Eights! (845 reputation)

Group: General Forum Members
Points: 845 Visits: 319
What I find more disturbing than this reliance on implicit conversion and thinking of a DATETIME value being held as a float is the reference to 12.00 AM. Surely everyone knows midnight and noon are neither PM nor AM.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search