Post reply

Not returning all rows using BETWEEN operator

  • May 6, 2005 at 3:12 pm

    #166907

    Greetings to the group...

    I've got a table with one field being a date field. In my query on that table, I use "between @dteFrom and @dteTo".

    BooksOnline says that the between will return anything greater than or equal the first variable and less than or equal to the second variable.

    The problem is I have several rows with a date of say, '05/06/2005'. I am passing that date to the @dteTo variable and those rows are not getting returned. If I change the variable to '05/07/2005' it returns all of the 05/06 rows.

    Am I missing something obvious?

    Thanks very much in advance for any light you can shed on this for me!

    Bob

  • May 6, 2005 at 4:07 pm

    #557474

    Its interesting.  I saw this exact same question just a day or so ago.  The reason is that your date column isn't a date column.  Its a datetime column. '05/07/2005' equates to '05/07/2005 12:00am'.  If you want to include everything from 5/7, you should add a day and use < @dteTo (if you continue to use 'between', you would need to add 11:59:59pm, which would leave the possibility of 11:59:59.001pm being excluded).

    Steve

  • May 6, 2005 at 8:44 pm

    #557480

    For an excellent article on how to work with sql server dates read this article by Frank Kalis:

    http://www.sql-server-performance.com/fk_datetime.asp

     

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

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