February 17, 2009 at 7:49 am
So the QotD answer was wrong but I learned something anyway -- I had forgotten about the rounding issue.
It reminded me of an Advanced Electromagnetic Fields class I took in college -- even the professor's answer key only got an 86% on the final exam.
- Randall Newcomb
February 17, 2009 at 8:04 am
I agree with Hugo K.'s point#s 3 and 6 in his p.1 reply, and because of the latter point (QotD used a colon between seconds and ms. rather than a dot), I figured the answer was "error." Oh well, that's where I learned something new :).
February 17, 2009 at 8:31 am
Points schmointz... who cares?
The takeaway is that SQL Server rounds time and don't use BETWEEN with a date range. But, this seems such a strange way to do this. What is the rationale? Can anyone explain that? Do other databases handle time the same way? It seems so counter-intuitive.
:unsure:
February 17, 2009 at 9:11 am
skjoldtc (2/17/2009)
Points schmointz... who cares?The takeaway is that SQL Server rounds time and don't use BETWEEN with a date range. But, this seems such a strange way to do this. What is the rationale? Can anyone explain that? Do other databases handle time the same way? It seems so counter-intuitive.
:unsure:
I think it actually comes down to how the data is stored in binary in the system, but I may be wrong.
February 17, 2009 at 10:22 am
I got the same wrong (but right) answer as everyone else (Option 4 only), but learned something while testing the result.
select '2009-02-01 00:00:00:000' union
select '2009-01-31 23:59:59:999'Produces 2 rows.
select convert(datetime,'2009-02-01 00:00:00:000') union
select convert(datetime,'2009-01-31 23:59:59:999')Only 1 row inserted.
Obviously, the UNION removal of duplicates is done as a character type in the first case, then the result is converted and rounded to produce 2 identical values, while in the second case, the conversion rounds both values to be the same before removing duplicates resulting in only 1 value!
Derek
February 17, 2009 at 11:30 am
I think this might have been better asked as 'what is the correct query to return the results we want?', instead of asking what return values would be possible (which I'd argue is bad form without first specifying that values are actually in the db in the first place).
Another potential way to get the same point across might have been to present us with several values in datetime format and ask which one will never appear in a db using the older (pre sql2008) datetime datatype..
Also illustrative is to use a bit of script to play with string values and see how they convert to datetime
[font="Courier New"]DECLARE @dt VARCHAR(22)
SET @dt = '20090131 23:59:59.999'
SELECT CAST(@dt AS DATETIME)[/font]
that results in [font="Courier New"]2009-02-01 00:00:00.000[/font] if you run it.
(edited to read a bit more clearly)
---
not a DBA just a QA guy who enjoys learning more about SQL
February 17, 2009 at 11:31 am
skjoldtc (2/17/2009)
The takeaway is that SQL Server rounds time and don't use BETWEEN with a date range. But, this seems such a strange way to do this. What is the rationale? Can anyone explain that? Do other databases handle time the same way? It seems so counter-intuitive.
My guess is that if you truncated the date, you would not exercise any index on [InsertedOn]. However, IMHBIO (in my humble but inexperienced opinion), I would question the wisdom of indexing a datetime. I stay away from indexing any inexact fields.
Kevin
--
Please upgrade to .sig 2.0
February 17, 2009 at 12:38 pm
OK, my head hurts.
I've awarded back points since there is obviously some issue here. I missed the correct answer with 1/30, so I've fixed that.
As far as the other items, I've changed the colon to a period for the ms, but I get a conversion error when adding the "T" in the date format. I've also changed the answers to show one correct answer.
apologies for the delays.
February 17, 2009 at 4:33 pm
skjoldtc (2/17/2009)
Points schmointz... who cares?The takeaway is that SQL Server rounds time and don't use BETWEEN with a date range. But, this seems such a strange way to do this. What is the rationale?
Hi skjoldtc,
The rationale for rounding? Or for not using BETWEEN?
For rounding, the rationale is that the internal presentation of datetime values uses two four-byte integer values, one for the number of whole days since Jan. 1st, 1900; and the other for the number of 1/300-second units (represented in textual format as .xx0, .xx3, and .xx7) after midnight. This internal presentation makes it impossible to store a value such as 23:59:59.999, so an attempt to set the time component of a datetime to this value can either cause an error or result in implicit rounding during the text-to-datetime conversion. I like the choice for rounding, since I'd hate to explain to my users that they can only enter datetime values with specific last numbers for the milliseconds. (Well, not that I expect my end users to manually key in a datetime with millisecond precision, but you get the point:)) Once the choice for rounding has been made, the next decision is to round always up, always down, or to the nearest representable value. Always down appears nice when you look at 23:59:59.999 specifically, but doesn't make much sense for any other value.
The rationale for not using BETWEEN follows logically from the above. But that's not the only reason. Even though SQL Server has some limitations in its representation, time is in essence a continuous measure. If you want to divide such a measure in intervals, the only correct way to do this is by using half-open intervals (i.e. with = 'A' and MyString < 'B'
Can anyone explain that?
If you're asking for an explanation on the 1/300 millisecond precision, I must disappoint you. I can only speculate. Until a few minutes ago, I had always believed it had to do with the maximum range of numbers that can be represented in a 4-bute integer. However, I just did the math, and I found that there are less than 85 million milliseconds to a day, but over 2 billion values in a 4-byte integer, so that is one theory down the drain 😀
My new theory is that is the heritage of some limitations of the timing hardware back in the early (Sybase) days of SQL Server.
Do other databases handle time the same way? It seems so counter-intuitive.
I have never heard of any other database storing time values in 1/300 second units. But I'm far from an expert on any other database, so who knows? Maybe others are even more weird? (But if I had to bet, I'd put my money on other databases having a more logical implementation... ;))
February 17, 2009 at 4:43 pm
knechod (2/17/2009)
My guess is that if you truncated the date, you would not exercise any index on [InsertedOn]. However, IMHBIO (in my humble but inexperienced opinion), I would question the wisdom of indexing a datetime. I stay away from indexing any inexact fields.
Hi Kevin,
I don't really understand what you mean. Since datetime has a higher precendence than char, the character constants will first be converted to datetime, and then the comparison can be made using the datatype of the column, so an index can be used. How exactly the string is converted to datetime doesn't affect this. Maybe I misunderstand your point?
Anyhow, never indexing a datetime is not a wise decision. Tables that are often queried with a date/time interval in the filter can often profit enormously from a clustered index on that datetime column. The same goes for tables where queries often include an ORDER BY clause for the datetime column. And if a table is often queries with a filter for an exact date/time value, then a nonclustered index can be very beneficial - the large number of possible values in a date/time column (provided it is not constrained to have the time value always equal to midnight, turning the datatype datetime in an effective equivalent of date only) almost guarantees high selectivity, which is an important factor in determining a nonclustered index's effectivity.
Finally, the datetime datatype is not inexact. It is basically a counter of the number of 1/300 second intervals since a fixed starting point (midnight of Jan 1st, 1900), so in many regards it is equivalent to an integer.
February 17, 2009 at 4:48 pm
Steve Jones - Editor (2/17/2009)
OK, my head hurts.I've awarded back points since there is obviously some issue here. I missed the correct answer with 1/30, so I've fixed that.
As far as the other items, I've changed the colon to a period for the ms, but I get a conversion error when adding the "T" in the date format. I've also changed the answers to show one correct answer.
apologies for the delays.
Hi Steve,
No need to apologize. You do a great job, day in day out, with keeping the site interersting. Errors will slip through - if only to prove that you are still human. (You are, are you?)
I'm intrigued by the error you got when you included the T. Can you post the EXACT value you used when you got that error? (Looking back at the question and answer, I remember that it already had two formats in use - one for loading the sample data, with dashes in between the date components; and one in the query, without the dashes. A well-formed datetime constant with time component needs both: dashes between the date components, AND an uppercase T between date and time. (Plus of course the colons between the time components and the dot before the milliseconds).
February 17, 2009 at 5:28 pm
Hugo Kornelis (2/17/2009)
Finally, the datetime datatype is not inexact. It is basically a counter of the number of 1/300 second intervals since a fixed starting point (midnight of Jan 1st, 1900), so in many regards it is equivalent to an integer.
Just to clarify this, it is not actually a count of 1/300ths of a second ('ticks') since some epoch. It is an 8-byte field composed of two 4-byte integers.
The first 4 bytes represent an integer, being the days after (or before - it's signed!) 01 Jan 1900.
The second 4 bytes is the number of 1/300ths of a second ('ticks') that have passed since midnight (up to 25,920,000 of them)
For collation purposes, they can be treated as a simple 8-byte integer, but be aware of the days-ticks split.
Note that there neither timezone or daylight saving information stored
(ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/9bd1cc5b-227b-4032-95d6-7581ddcc9924.htm)
Also, IIRC, IBM mainframes (360 & 370 anyway) updated their TOD clock every 300th of a second, to a precision of better than a microsecond. That was an interesting example of the difference between precision and accuracy!
February 17, 2009 at 8:19 pm
Hugo Kornelis (2/17/2009)
Anyhow, never indexing a datetime is not a wise decision.
More of an avoidance than a hard-and-fast rule. As an ordering method, not a problem. As a clustered index, ok. I'm a little concerned about how a clustered datetime would affect multiple users inserting at the same time. But try querying for 2/16/09 8:17pm (the time right now MST), in a table where the datetime is acting like a timestamp. It's more like indexing a real number.
As for high selectivity, I DO index GUIDs. But I would never make a user enter one!
Finally, the datetime datatype is not inexact.
Yes, dates are integers at some level. But my users' concept of dates don't extend to 3.333 milliseconds.
Now, confession time. I actually LIKE Oracle's approach of storing the time as the decimal portion of a floating point number, where it's the fraction of the day. Maybe that's where the aversion for indexing dates comes from.
Kevin
--
Please upgrade to .sig 2.0
February 18, 2009 at 12:36 am
And actually there are two answers.
2009-01-31 00:00:00.000, 2009-01-31 00:01:01.000, 2009-01-31 12:59:59.128
2009-01-30 00:00:00.000, 2009-01-31 00:01:01.000, 2009-01-31 13:00:00.995
2009-01-30 00:00:00:000, 2009-01-31 00:01:01:127
2009-01-31 00:00:00.000, 2009-01-31 00:01:01.000, 2009-01-31 21:59:58.347
2009-02-1 00:01:01.000, 2009-01-31 21:59:58.347, 2009-01-31 23:59:59.999
2009-02-01 00:00:00:003 2009-01-31 09:01:30:347 2009-01-31 23:59:59:999
Msg 241, Level 16, State 1, Line 1 Conversion failed when converting datetime from character string.
so, moderators should not consider the question or give us a bonus point.
Ramu
No Dream Is Too Big....!
February 18, 2009 at 1:09 am
ramu.valleti (2/18/2009)
And actually there are two answers.{emphasis changed by brewmanz}
2009-01-31 00:00:00.000, 2009-01-31 00:01:01.000, 2009-01-31 12:59:59.128
...
2009-01-31 00:00:00.000, 2009-01-31 00:01:01.000, 2009-01-31 21:59:58.347
Please explain how you got 2009-01-31 12:59:59.128 (as in, ending with .128)
I can only get ending in .127 or .130 when looking around .128
Viewing 15 posts - 31 through 45 (of 52 total)
You must be logged in to reply to this topic. Login to reply