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.
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.