August 2, 2012 at 9:59 pm
Great................!:-P
August 2, 2012 at 10:00 pm
Great....................!:-P
August 3, 2012 at 3:05 am
This was removed by the editor as SPAM
August 4, 2012 at 1:36 pm
Hi Ron,
I did not get it. as i am new. please let me know why dis has happned and which rows would be displayed for each?
i also went thru the msdn link but din get much understanding.
would be grateful.
August 4, 2012 at 3:22 pm
The question is great. But I don't really like the presentation, for two reasons:
1. The order of presentation - putting the SELECT before the CREATE TABLE and INSERT statements adds confusion that serves no apparent purpose.
2. The layout of the INSERT statements. My first thought was that there was a copy/paste error, because I saw three INSERT VALUES statements and there were answer options that mentioned four rows returned. I had to look very careful to see the trick. Using either six INSERT statements or one single INSERT statement would have been a lot clearer and would have allowed me to focus on the actual subject being tested.
vips8nov (8/4/2012)
I did not get it. as i am new. please let me know why dis has happned and which rows would be displayed for each?i also went thru the msdn link but din get much understanding.
I hope you don't mind if I answer this?
There are two possibly confusing issues in this question. One is about precision of datetime2 values, the other about interpretation of the BETWEEN.
Let's look at the precision first. The following datetime values are inserted in both tables:
1. June 30, 23:59:59.9999999
2. July 30, 15:32:00.0000000
3. July 30, 23:59:59.9999999
4. July 31, 0:00:00.0000000
5. July 31, 23:59:59.9999999
6. August 1, 0:00:00.0000000
Table D7 has the OrderDate column defined as datetime2(7), which means 7 positions of fractional seconds are retained (this is also the default for datetime2, if I recall correctly). All values given use 7 fractional positions, so all values are stored as given.
Table D4 however has OrderDate defined as datetime2(4). Only 4 fractional positions are retained, so the values given are rounded (rounding instead of truncating is a design choice that is probably documented somewhere in BOL - I'm just too lazy to hunt down a link now). So values 1, 3, and 5 are rounded up to respectively July 1st, 0:00:00; July 31st, 0:00:00; and August 1st, 0:00:00.
The queries used include WHERE OrderDate BETWEEN '20110701' AND '20110731'. A human reader would interpret this as the entire month of July, 2011 (**). But SQL Server is not a human reader. In SQL Server, a date without time portion is assumed to be 0:00:00 of that date. And BETWEEN means that the boundary values are included, so this WHERE clause is synonym to WHERE OrderDate >= '20110701 0:00:00' AND OrderDate <= '20110731 0:00:00' (with the appropriate number of zeroes for fractional seconds). Or in human people languages, all orders placed at any time on dates from July 1st up to and including July 30, plus orders placed at the very first microsecond (or millisecond for the T4 table) of July 31.
For the D7 table, rows 2, 3, and 4 match this. Row 1 is before 20110701 0:00:00; row 5 and later are after 20110731 0:00:00.
For the D4 table, rows 1, 2, 3, and 4 match. Row 1 now is included, because the time is rounded up to fall in the interval; rows 2, 4 and 6 are not affected by the rounding; and row 3 is rounded up from just before the end of the interval to exactly the end of the interval (so it is still included). Row 5 is rounded up as well, but it already was a day (minus a fraction of a second) late, so that doesn't affect the results.
(**) Since getting all data for a specific month is a common requirement that is very often coded wrong, here is the only truly safe way to find all orders that are placed inthe month of July 31 (regardless of time):
WHERE OrderDate >= '20110701' AND OrderDate < '20110801'
You can use BETWEEN, but it is awkward because you have to specify the upper limit of the period with the exact right number of fractional seconds, depending on data type used, and it's dangeroud because you have to remember change it when the data type is ever changed. You can also use date functions such as MONTH or date conversion functions, but that would severly reduce the usefulness of any index on the datatime column.
August 5, 2012 at 6:00 am
Hugo Kornelis (8/4/2012)
Or in human people languages, all orders placed at any time on dates from July 1st up to and including July 30, plus orders placed at the very first microsecond (or millisecond for the T4 table) of July 31.
Very clear explanation, Hugo. I thinkl it will help a lot of people. But there is a minor error in the entence I've quoted: "at the very first microsecond" should be "in the very first 20th of a microsecond" (because 4 places precision is accurate to 1/10 or a millisecond, and the fact that times are rounded means the overrun from the boundary only half of that. (and of course the same 20th for microsecond).
Tom
August 5, 2012 at 7:09 am
L' Eomot Inversé (8/5/2012)
But there is a minor error in the entence I've quoted: "at the very first microsecond" should be "in the very first 20th of a microsecond" (because 4 places precision is accurate to 1/10 or a millisecond, and the fact that times are rounded means the overrun from the boundary only half of that. (and of course the same 20th for microsecond).
:rolleyes: I *knew* someone would call me out on that.
I decided to simplfy this because my explanation was already approaching novelette length. And I thought it would be interesting to see how long it would take for someone to correect me, and who it would be. 😉
August 5, 2012 at 11:42 am
Hugo,
You answered my question! THANKS A LOT! 🙂
wanna follow u n ur posts! (dun get me wrong ;-))
August 6, 2012 at 12:44 am
Thanks Ron. This question made me think a bit.
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
September 4, 2014 at 7:19 am
You should provide the table and insert script for the same
September 4, 2014 at 7:28 am
tbs.jignesh (9/4/2014)
You should provide the table and insert script for the same
Why? So you could cheat on the question?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 11 posts - 31 through 40 (of 40 total)
You must be logged in to reply to this topic. Login to reply