Pulling incorrect records using date range in where clause

  • Hi All,

    I've been experiencing difficulty with pulling records using a where clause date range. I'm using this:

    select *

    from dbo.ACCTING_TRANSACTION_hISTORY

    where ath_postype = 'NTC' or ath_postype='NTD' and

    ath_postdate >= '2013-01-01 00:00:00' and

    ath_postdate <= '2013-01-05 23:59:59'

    I've also tried variations of this without the time portion of the ath_postdate field (of type datetime) , but it still seems to be pulling reocrds from 2009, etc. and I'm not sure why. I appreciate your insight. Thanks.

  • The AND operator has a higher precedence than the OR operator leading to the following logic:

    where (ath_postype = 'NTC') or (ath_postype='NTD' and

    ath_postdate >= '2013-01-01 00:00:00' and

    ath_postdate <= '2013-01-05 23:59:59')

    If this is not your intention, I'd recommend using parenthesis:

    where (ath_postype = 'NTC' or ath_postype='NTD')

    and ath_postdate >= '2013-01-01 00:00:00'

    and ath_postdate <= '2013-01-05 23:59:59'



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • Thanks, LutzM. That did the trick. 🙂

  • Btw, you should < on date/datetime, not <=, as below. Otherwise you risk missing rows, especially if the data type of the underlying column changes (to datetime from smalldate, datetime2 from datetime, etc.).

    Also, 'YYYYMMDD' is the only 100% safe date format (excluding obscure versions with 'T' in them).

    ath_postdate >= '20130101' and

    ath_postdate < '20130106'

    Edit: I'm assuming it was Jan 05 not May 01 (demonstrating the ambiguity of the other format :-)).

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • 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'

    -------------------------------------------------------------
    we travel not to escape life but for life not to escape us
    Don't fear failure, fear regret.

  • 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'?

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

    -------------------------------------------------------------
    we travel not to escape life but for life not to escape us
    Don't fear failure, fear regret.

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

    Best way to query a range of dates is to use a closed end on the lower end (SomeDateCol >= '20140101') and an open end on the upper end of the range (< '20140106').

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

    No, you can't accurately change it to "23:59.59.999" either, because that rounds up to 00:00:00.000.

    That's why I urged earlier, and others also, you should always use "<" on the ending range.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • Good to know, I wasn't aware that it rounded up.

    It is wierd that if you do .998 it shows as .997.

    declare @d datetime;

    set @d = '2014-04-23 23:59:59.998'

    select @d

    -------------------------------------------------------------
    we travel not to escape life but for life not to escape us
    Don't fear failure, fear regret.

  • Thanks for all the helpful input, everyone. 🙂

  • below86 (4/23/2014)


    Good to know, I wasn't aware that it rounded up.

    It is wierd that if you do .998 it shows as .997.

    declare @d datetime;

    set @d = '2014-04-23 23:59:59.998'

    select @d

    0

    This comes from the fact that datetime is accurate within 3.333ms. So .995 rounds up to .997, .992 rounds up to .993 , etc...

    Datetime in BOL

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • Matt Miller (#4) (4/23/2014)


    below86 (4/23/2014)


    Good to know, I wasn't aware that it rounded up.

    It is wierd that if you do .998 it shows as .997.

    declare @d datetime;

    set @d = '2014-04-23 23:59:59.998'

    select @d

    0

    This comes from the fact that datetime is accurate within 3.333ms. So .995 rounds up to .997, .992 rounds up to .993 , etc...

    Datetime in BOL

    So If I change my code to use the .998 I would never miss any records, since a record would never have .999, it would be rounded up to the next day.

    (YYYY-MM-DD)

    BETWEEN '2014-04-01 00:00:00.000' AND '2014-04-30 23:59:59.998'

    This would get all possible records where this date is in April 2014.

    I know we have between coded in some area's, but I think that is after the data is loaded to our warehouse, and I 'm pretty sure the ETL strips off the ms. (Didn't set it up, so I don't know why.)

    -------------------------------------------------------------
    we travel not to escape life but for life not to escape us
    Don't fear failure, fear regret.

  • below86 (4/23/2014)


    Matt Miller (#4) (4/23/2014)


    below86 (4/23/2014)


    Good to know, I wasn't aware that it rounded up.

    It is wierd that if you do .998 it shows as .997.

    declare @d datetime;

    set @d = '2014-04-23 23:59:59.998'

    select @d

    0

    This comes from the fact that datetime is accurate within 3.333ms. So .995 rounds up to .997, .992 rounds up to .993 , etc...

    Datetime in BOL

    So If I change my code to use the .998 I would never miss any records, since a record would never have .999, it would be rounded up to the next day.

    (YYYY-MM-DD)

    BETWEEN '2014-04-01 00:00:00.000' AND '2014-04-30 23:59:59.998'

    This would get all possible records where this date is in April 2014.

    I know we have between coded in some area's, but I think that is after the data is loaded to our warehouse, and I 'm pretty sure the ETL strips off the ms. (Didn't set it up, so I don't know why.)

    Until you change data types from datetime to datetime2 where the accuracy increases to 100 micro seconds. Again, for selecting records for a range of date time values using a range that is closed at the lower end and open ended at the upper end (somedatecol >= '20140101' and somedatecol < '20140201' to return all records in January 2014) is really the way to go.

  • 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)

Viewing 15 posts - 1 through 15 (of 18 total)

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