Post reply

Odd Behavior with Dates

  • September 28, 2007 at 7:22 am

    #153864

    I'm running SS 2000. THe following two queries return differnet results and I was trying to figure out why.

    select sum(funetprice * (forderqty-(fshipbook+fshipmake+fshipbuy)))

    FROM mm_ot_detail

    WHERE fmasterrel = '0' and fcustno not in ('000430','M02325')

    and sorelsDueDate between '2007-9-27 23:59:59.999' and '2007-9-28 23:59:59.999';

    select sum(funetprice * (forderqty-(fshipbook+fshipmake+fshipbuy)))

    FROM mm_ot_detail

    WHERE fmasterrel = '0' and fcustno not in ('000430','M02325')

    and sorelsDueDate > '2007-9-27 23:59:59.999' and sorelsDueDate <= '2007-9-28 23:59:59.999';

    The rows in the table the query it is running against all have the date column data as 2007-09-28 00:00:00.000 that I'm trying to retrieve.

    If I change the > <= query dates to '2007-9-27 23:59:59.000' then it works. (changing the 999 at end to 000). Why is that? It looks like SS is rounding the date up. I'm just trying to understand as this greatly changes my results. Why doesn't the between round up if the other is?

    Thanks

    Thanks

  • September 28, 2007 at 7:30 am

    #737109

    You can find an explanation about how milliseconds are rounded in BOL if you search for 'datetime data type, overview [SQL Server - T-SQL]'

    It looks like you're trying to get all rows that have the 28th as date?

    Does it work better if you try;

    WHERE datecolumn >= '20070928' and < '20070929'

    /Kenneth

  • September 28, 2007 at 7:48 am

    #737119

    Yes, that works as well. I'll take a look at that explanation as well. It just seemed odd that it would round.

    I read the explanation, now it makes sense for why .999 = .000

    Thanks

    Thanks

  • September 28, 2007 at 8:27 am

    #737133

    Also read this article about DATEDIFF

    http://www.sqlteam.com/article/datediff-function-demystified

    N 56°04'39.16"
    E 12°55'05.25"

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply