SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Sunil Chandurkar / DateTime Puzzle / Latest Posts

RE: DateTime Puzzle

Britt Cluff — Thu, 17 May 2012 20:06:18 GMT

Good question. Thanks for submitting.

RE: DateTime Puzzle

zymos — Thu, 17 May 2012 13:14:51 GMT

Easy and straight forward one. Thanks

RE: DateTime Puzzle

David Conn — Wed, 09 May 2012 00:28:33 GMT

The sooner we stop using datetime the better. We now have Date & Time Data Types and DateTime2. It's fairly straight forward: Dates and Times should be stored as such. If a Timestamp is required then use Datetime2 with its correct level of accuracy.

RE: DateTime Puzzle

Paul White — Tue, 08 May 2012 09:33:06 GMT

[quote][b]marlon.seton (5/8/2012)[/b][hr]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.[/quote]This article sums the situation up quite well: [url]http://en.wikipedia.org/wiki/12-hour_clock#Confusion_at_noon_and_midnight[/url]Even if we take the position that AM and PM, taken literally from their meanings in Latin, do not apply to midnight and noon, there are still certain practicalities to consider. For example, certain CONVERT styles specify AM and PM. By convention, we set midnight as AM and noon as PM:[code="sql"]SELECT CONVERT(char(26), CONVERT(datetime, '2012-07-11 00:00:00', 120), 109);SELECT CONVERT(char(26), CONVERT(datetime, '2012-07-11 12:00:00', 120), 109);[/code]

RE: DateTime Puzzle

KWymore — Tue, 08 May 2012 08:20:01 GMT

Nice question, even better discussion. Thanks all!

RE: DateTime Puzzle

marlon.seton — Tue, 08 May 2012 08:09:30 GMT

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.

RE: DateTime Puzzle

Andrew Watson-478275 — Tue, 08 May 2012 04:00:55 GMT

[quote][b]Toreador (5/8/2012)[/b][hr]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![/quote]It's one of those cases where it's really easy - [u]if[/u] 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.

RE: DateTime Puzzle

Toreador — Tue, 08 May 2012 02:40:21 GMT

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!

RE: DateTime Puzzle

Paul White — Mon, 07 May 2012 19:29:53 GMT

[quote][b]Nils Gustav Stråbø (5/7/2012)[/b][hr][quote][b]SQL Kiwi (5/5/2012)[/b]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:[/quote]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.[/quote]Thanks! Of course that's right - not sure how I managed to confuse myself before. Must be age catching up with me :-)

RE: DateTime Puzzle

EL Jerry — Mon, 07 May 2012 18:28:38 GMT

Nice easy question to begin the week."El" Jerry.

RE: DateTime Puzzle

Lon-860191 — Mon, 07 May 2012 08:45:22 GMT

Thanks, an easy way to start this week.

RE: DateTime Puzzle

Revenant — Mon, 07 May 2012 08:35:08 GMT

A 5-second question... Thanks!

RE: DateTime Puzzle

SQLRNNR — Mon, 07 May 2012 08:21:31 GMT

[quote][b]SQL Kiwi (5/5/2012)[/b][hr]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):[code="sql"]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;[/code]If the intention has been to show a float, either a float literal or a typed variable could have been used:[code="sql"]SELECT CAST(25e-2 AS datetime);DECLARE @f float = 25e-2;SELECT CAST(@f AS datetime);[/code]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 - [url=http://msdn.microsoft.com/en-us/library/ms187928.aspx]CAST and CONVERT (Transact-SQL)[/url]). 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:[code="sql"]DECLARE @dt datetime = '1900-01-02 00:00:00.006';SELECT @dt;SELECT CONVERT(binary(8), @dt);[/code]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.[/quote]+1

RE: DateTime Puzzle

Nils Gustav Stråbø — Mon, 07 May 2012 07:58:12 GMT

[quote][b]SQL Kiwi (5/5/2012)[/b]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:[/quote]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.

RE: DateTime Puzzle

Thomas Abraham — Mon, 07 May 2012 05:51:43 GMT

[quote][b]SQL Kiwi (5/5/2012)[/b]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 - [url=http://msdn.microsoft.com/en-us/library/ms187928.aspx]CAST and CONVERT (Transact-SQL)[/url]). 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:[code="sql"]DECLARE @dt datetime = '1900-01-02 00:00:00.006';SELECT @dt;SELECT CONVERT(binary(8), @dt);[/code]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.[/quote]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.

RE: DateTime Puzzle

Paul White — Mon, 07 May 2012 04:55:15 GMT

[quote][b]Hugo Kornelis (5/7/2012)[/b][hr]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.[/quote]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.

RE: DateTime Puzzle

Stewart "Arturius" Campbell — Mon, 07 May 2012 02:59:43 GMT

Thanks for the simple question.However, as stated by Paul, the idea that DATETIME et al are stored as FLOAT is highly disturbing.

RE: DateTime Puzzle

IgorMi — Mon, 07 May 2012 01:14:45 GMT

The answer was easy. I also appreciate the discussion from SQL Kiwi.ThanksIgorMi

RE: DateTime Puzzle

IgorMi — Mon, 07 May 2012 01:14:45 GMT

The answer was easy. I also appreciate the discussion from SQL Kiwi.ThanksIgorMi

RE: DateTime Puzzle

IgorMi — Mon, 07 May 2012 01:14:44 GMT

The answer was easy. I also appreciate the discussion from SQL Kiwi.ThanksIgorMi

RE: DateTime Puzzle

Hugo Kornelis — Mon, 07 May 2012 00:45:21 GMT

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>

RE: DateTime Puzzle

Koen Verbeeck — Mon, 07 May 2012 00:28:42 GMT

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

RE: DateTime Puzzle

Paul White — Sat, 05 May 2012 19:40:27 GMT

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):[code="sql"]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;[/code]If the intention has been to show a float, either a float literal or a typed variable could have been used:[code="sql"]SELECT CAST(25e-2 AS datetime);DECLARE @f float = 25e-2;SELECT CAST(@f AS datetime);[/code]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 - [url=http://msdn.microsoft.com/en-us/library/ms187928.aspx]CAST and CONVERT (Transact-SQL)[/url]). 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:[code="sql"]DECLARE @dt datetime = '1900-01-02 00:00:00.006';SELECT @dt;SELECT CONVERT(binary(8), @dt);[/code]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.

RE: DateTime Puzzle

bitbucket-25253 — Sat, 05 May 2012 17:21:32 GMT

Thanks, a nice easy question

DateTime Puzzle

Sunil Chandurkar — Sat, 05 May 2012 17:20:40 GMT

Comments posted to this topic are about the item [B]DateTime Puzzle[/B]