2

I am trying to select rows that are in between two dates. First, here is my data:

punch_id    eid     time    unixtime    punch_type  day     date        doy
135          2      12:53   1314723193  0            4   08/28/2011     241  
134          2      12:53   1314723190  3            4   08/31/2011     241
133          2      12:53   1314723187  2            4   08/20/2011     241
132          2      12:52   1314723125  1            4   08/30/2011     241

I have tried these two queries.

SELECT * FROM `punches` WHERE `date` >= '08/20/11' AND `date` <= '08/31/11'

SELECT * FROM `punches` WHERE `date` BETWEEN '08/20/11' AND '08/31/11'

Neither of these select the rows containing the date 08/31/11. It selects the 08/20/11 ones though. I tried to use it another way and when I run the query:

SELECT * FROM `punches` WHERE `date` >= '08/10/11' AND `date` <= '08/20/11'

I again do not get the correct result: the 20th is left out once again. What is the problem with the way I am executing this?

Devin
  • 2,146
  • 5
  • 24
  • 36

4 Answers4

3

See this related question.

As others have mentioned, your primary problem is not accounting for the time. A few options to handle that:

  1. Use a function to convert the DateTime to a Date. I don't recommend this option, since it will likely make the function non-sargeable.

  2. Expand your BETWEEN to explicitly include the last moment of the day: (note: this is the latest possible value that MS SQL can store, don't know if MySQL has the same value)

    SELECT * FROM `punches` WHERE `date` 
BETWEEN '08/20/11 00:00:00.000' AND '08/31/11 23:59:59.997'

  3. Use a < for the upper value

    SELECT * FROM `punches` WHERE `date` >= '08/20/11' AND `date` < '09/01/11'

I actually think that last one is easier, in most situations.

I suppose you could do other things, like change the datatype of the column, but I've assumed here that you're just interested in changing the query.

** Disclaimer: I'm a MS SQL guy, not MySQL

Community
  • 1
  • 1
BradC
  • 39,306
  • 13
  • 73
  • 89
  • That works, but is pretty much a 'hack', and would require a lot of work to make the application add a day to all queries. I'd rather fix it and do it right by changing the date format or something to make it work. – Devin Aug 30 '11 at 18:01
  • 2
    @Devin, if needed you could do something like `where 'date' between "2011-08-20" and date_add( "2011-08-31" , interval 1 day)` – Inca Aug 30 '11 at 18:27
1

I don't know mysql, but in other RDBMS, dates are assuming a time part of 12 AM. If you want to include the high date, add 1 to the day.

Ian Jacobs
  • 5,456
  • 1
  • 23
  • 38
1

Is your date field of type DATE or DATETIME?

It probably has to do with the time of day. If you have a DATETIME stored as

2011-08-31 13:00:00

then it won't match on

BETWEEN '08/20/11' AND '08/31/11'

You'd have to use 

BETWEEN '08/20/11' AND '09/01/11'

The MySQL docs for between say it matches "less than or equal to" max, so it is probably the time of day throwing you off.

JJ.
  • 5,425
  • 3
  • 26
  • 31
1

When you don't specify a time with the date, then 00:00:00 is implied. Therefore the real query that the database is doing is more like...

SELECT * FROM `punches` WHERE `date` BETWEEN '08/20/11 00:00:00' AND '08/31/11 00:00:00'

Therefore a punch on 08/31/2011 at 12:53 will not get included. I think this should work, and is a bit more elegant than adding a day to the end date...

SELECT * FROM `punches` WHERE DATE(`date`) BETWEEN '2011-08-20' AND '2011-08-31'
philwilks
  • 669
  • 5
  • 16