Home Forums SQL Server 2008 T-SQL (SS2K8) Pulling incorrect records using date range in where clause RE: Pulling incorrect records using date range in where clause

  • April 23, 2014 at 9:20 pm

    #1708292

    below86 (4/23/2014)

    Lynn Pettis (4/23/2014)

    below86 (4/23/2014)

    Wouldn't it be better to use the BETWEEN with these dates?

    WHERE (ath_postype = 'NTC' OR ath_postype='NTD')

    AND ath_postdate BETWEEN '2013-01-01 00:00:00.000' AND '2013-01-05 23:59:59.000'

    And what happens if the ath_postdate contains the value '2013-01-05 23:59:59.133'?

    I knew that was coming as soon as I hit 'Post'. 🙂 In our case, at least as of right now, the dates don't carry the '.133'. I guess I could just change it to '2013-01-05 23:59.59.999'. My question was more towards using the BETWEEN than coding it with the >= and <=.

    As others have already stated, that would actually round up to 2013-01-06 if the column were a DATETIME column because the resolution of DATETIME is 3.3ms. The BEST way (and I don't say that often) is to use the method that Scott and Lynn have both suggested. That way, it doesn't matter what the underlying DT datatype is... ever.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)