Sunday, May 17, 2009

Date Between Syntax Bug from MS SQL Server 2005?

1. select TradeDate from test where TradeDate between '2009-05-13 00:00:00.000' and '2009-05-13 23:59:59.999'
result: 
2009-05-14 00:00:00.000
2009-05-14 00:00:00.000
2009-05-14 00:00:00.000
2009-05-14 00:00:00.000
2009-05-14 00:00:00.000
(5 row(s) affected)


2. select TradeDate from test where TradeDate between '2009-05-13 00:00:00.000' and '2009-05-13 23:59:59.998'
result: 
(0 row(s) affected)


3. select TradeDate from test where TradeDate between '2009-05-13 00:00:00.000' and '2009-05-14 00:00:00.000'
result: 
2009-05-14 00:00:00.000
2009-05-14 00:00:00.000
2009-05-14 00:00:00.000
2009-05-14 00:00:00.000
2009-05-14 00:00:00.000
(5 row(s) affected)
Posted by at
Labels: , ,

2 comments:

  1. Isaac KunenMay 17, 2009 at 6:19 PM

    Hi Henry,

    These results look expected. Since you're on 2005, I assume TradeDate is of type datetime, which as a precision of 1/300th of a second. From BOL:

    "datetime values are rounded to increments of .000, .003, or .007 seconds..."

    So your value "2009-05-13 23:59:59.999" from example 1 is being rounded to "2009-05-14 00:00:00.000" before any comparisons happen. From there the results are pretty clear.

    FYI, the datetime2 type in SQL 2008 has much better precision, and so this example should work as expected. (Although you could make another strange example like this for that type as well.)

    Cheers,
    Isaac (SQL Server PM)

    ReplyDelete
  2. HenryMay 19, 2009 at 3:18 PM

    Hi Isaac,

    Thanks for your kindly answer. But should I continute to use 'between' on datetime type? Or other RDBMS has the same issue?

    ReplyDelete

Subscribe to: Post Comments (Atom)