none
Where clause and use between two dates RRS feed

  • Question

  • Hello all,

    I put in a where clause, the dates between '01/01/2015' and '12/31/2015' to filter the data. The result was different than when I put in where clause: Year(Date).

    Between is inclusive. Why are the result different?

    Regards,

    BittenApple


    CloudsInSky

    Thursday, July 11, 2019 10:10 PM

Answers

  • Hi,

    If foobar_date is only date both should be the same, but if its datetime maybe there are rows between 2015-12-31 and 2015-12-31 23:59:59, check that.

    Hope it helps.

    Ishiro

    • Marked as answer by cloudsInSky Thursday, July 11, 2019 11:02 PM
    Thursday, July 11, 2019 10:52 PM
  • Hello Ishiro,

    I understand what you say. Just from database engine perspective:

    Are you saying because the data type for the column is dataTime, then a date to be like 2015-12-31 23:59:59 is ignored by database engine if where clause is entered as: WHERE foobar_date BETWEEN 2015-01-01 AND 2015-12-31?

    Respectfully,

    bittenApple


    CloudsInSky

    Yes - if the column is datetime then any values for that column that have a time greater than 0 will not be included in the results.

    The value '2015-01-01' is implicitly converted to the date/time value '2015-01-01 00:00:00.000' and the value '2015-12-31' is implicitly converted to the date/time value '2015-12-31 00:00:00.000'

    The between statement therefore is: BETWEEN '2015-01-01 00:00:00.000' AND '2015-12-31 00:00:00.000'

    If the column value is '2015-12-31 00:00:00.003' - that value is NOT between the 2 dates and will be excluded.

    The best way to handle this is to use an open-interval range check instead of between:

    WHERE column >= '2015-01-01 00:00:00.000'

    AND column < dateadd(day, 1, '2015-12-31 00:00:00.000')

    This will include all dates up to but not including 2016-01-01.


    Jeff Williams

    • Marked as answer by cloudsInSky Friday, July 12, 2019 11:42 PM
    Friday, July 12, 2019 6:44 PM
  • Hello Jeff,

    Thanks for the response. I got what I wanted.

    I think the best way is, it is only what I think.

    Where column between '2015-01-01 00:00:00.000' And '2015-01-01 23:59:59.000' 

    This can work as well:

    The best way to handle this is to use an open-interval range check instead of between:

    WHERE column >= '2015-01-01 00:00:00.000'

    AND column < dateadd(day, 1, '2015-12-31 00:00:00.000')


    CloudsInSky

    • Marked as answer by cloudsInSky Monday, July 15, 2019 6:16 AM
    Friday, July 12, 2019 11:47 PM

All replies

  • Since you didn't bother to post DDL, can we guess that the column is DATE data type? This could be done with "WHERE foobar_date BETWEEN 2015-01-01 AND 2015-12-31" and not bother with the proprietary year() function.

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Thursday, July 11, 2019 10:28 PM
  • Thanks for response.

    Yes, correct, the data type for date column is date.

    The result should be same thing:

    WHERE foobar_date BETWEEN 2015-01-01 AND 2015-12-31

    and

    Where YEAR(foobar_date ) = 2015

    But the result is not same. Although Between ... And... is inclusive.

    Respectfully,

    BittenApple


    CloudsInSky

    Thursday, July 11, 2019 10:38 PM
  • Hi,

    If foobar_date is only date both should be the same, but if its datetime maybe there are rows between 2015-12-31 and 2015-12-31 23:59:59, check that.

    Hope it helps.

    Ishiro

    • Marked as answer by cloudsInSky Thursday, July 11, 2019 11:02 PM
    Thursday, July 11, 2019 10:52 PM
  • Hello Ishiro,

    I understand what you say. Just from database engine perspective:

    Are you saying because the data type for the column is dataTime, then a date to be like 2015-12-31 23:59:59 is ignored by database engine if where clause is entered as: WHERE foobar_date BETWEEN 2015-01-01 AND 2015-12-31?

    Respectfully,

    bittenApple


    CloudsInSky

    Thursday, July 11, 2019 11:19 PM
  • Use this:

    WHERE foobar_date>='2015-01-01' AND  foobar_date <'2016-01-01'

    Friday, July 12, 2019 1:28 PM
    Moderator
  • Hello Ishiro,

    I understand what you say. Just from database engine perspective:

    Are you saying because the data type for the column is dataTime, then a date to be like 2015-12-31 23:59:59 is ignored by database engine if where clause is entered as: WHERE foobar_date BETWEEN 2015-01-01 AND 2015-12-31?

    Respectfully,

    bittenApple


    CloudsInSky

    Yes - if the column is datetime then any values for that column that have a time greater than 0 will not be included in the results.

    The value '2015-01-01' is implicitly converted to the date/time value '2015-01-01 00:00:00.000' and the value '2015-12-31' is implicitly converted to the date/time value '2015-12-31 00:00:00.000'

    The between statement therefore is: BETWEEN '2015-01-01 00:00:00.000' AND '2015-12-31 00:00:00.000'

    If the column value is '2015-12-31 00:00:00.003' - that value is NOT between the 2 dates and will be excluded.

    The best way to handle this is to use an open-interval range check instead of between:

    WHERE column >= '2015-01-01 00:00:00.000'

    AND column < dateadd(day, 1, '2015-12-31 00:00:00.000')

    This will include all dates up to but not including 2016-01-01.


    Jeff Williams

    • Marked as answer by cloudsInSky Friday, July 12, 2019 11:42 PM
    Friday, July 12, 2019 6:44 PM
  • This is can't contain all the range when the datatype is dateTime.

    Respectfully,

    Guity


    CloudsInSky

    Friday, July 12, 2019 11:40 PM
  • Hello Jeff,

    Thanks for the response. I got what I wanted.

    I think the best way is, it is only what I think.

    Where column between '2015-01-01 00:00:00.000' And '2015-01-01 23:59:59.000' 

    This can work as well:

    The best way to handle this is to use an open-interval range check instead of between:

    WHERE column >= '2015-01-01 00:00:00.000'

    AND column < dateadd(day, 1, '2015-12-31 00:00:00.000')


    CloudsInSky

    • Marked as answer by cloudsInSky Monday, July 15, 2019 6:16 AM
    Friday, July 12, 2019 11:47 PM