Glen Parker (2/17/2009)
Given that SQL is logic based, its a shame that the result was so ambiguous...Like most, I got it wrong too however given the explanation, surely the data capture is between:
2009-01-30 23:59:59.007 AND 2009-01-31 23:59:59.007
and therefore option 'C' is the only correct selection.
Hi Glen,
No, that's not correct. The upper limit in the BETWEEN proposition (2009-01-31 23:59:59.999) has to be converted to a proper datetime value. It can either be rounded down (to 2009-01-31 23:59:99.997), or up (to 2009-02-01 0:00:00.000). The rounding will go to the nearest value that can be represented, so in this case the rounding will be up. After the conversion, the query will work as if it was stated as "... BETWEEN ... AND 2009-02-01 0:00:00.000, and hence the latter datetime value will be returned as well.