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 Monday, May 7, 2012 5:51 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, November 12, 2014 9:31 AM
Points: 1,890, Visits: 2,192
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
Post #1295882
Posted Monday, May 7, 2012 7:58 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, October 31, 2014 12:07 PM
Points: 1,880, Visits: 3,463
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.
Post #1295917
Posted Monday, May 7, 2012 8:21 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 12:45 PM
Points: 17,947, Visits: 15,941
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
Post #1295926
Posted Monday, May 7, 2012 8:35 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Friday, October 24, 2014 12:43 PM
Points: 4,126, Visits: 3,428
A 5-second question... Thanks!
Post #1295932
Posted Monday, May 7, 2012 8:45 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 11:51 AM
Points: 1,477, Visits: 254
Thanks, an easy way to start this week.
Post #1295937
Posted Monday, May 7, 2012 6:28 PM
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: Wednesday, November 19, 2014 1:50 PM
Points: 3,798, Visits: 1,221
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.
Post #1296170
Posted Monday, May 7, 2012 7:29 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: 2 days ago @ 7:53 PM
Points: 9,928, Visits: 11,194
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1296184
Posted Tuesday, May 8, 2012 2:40 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 9:26 AM
Points: 1,802, Visits: 6,571
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!
Post #1296290
Posted Tuesday, May 8, 2012 4:00 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 3:11 AM
Points: 1,287, Visits: 2,261
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.
Post #1296336
Posted Tuesday, May 8, 2012 8:09 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 8:03 AM
Points: 825, 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.
Post #1296479
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse