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?

Improve this question
3
  • How is your date column defined? With or without the hh:mm:ss part? Commented Aug 30, 2011 at 17:52
  • The application simply outputs it in the format listed. MM/DD/YY Commented Aug 30, 2011 at 17:58
  • exact duplicate of stackoverflow.com/questions/5080824/…. Commented Aug 30, 2011 at 18:15

4 Answers 4

Reset to default
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

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

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, if needed you could do something like where 'date' between "2011-08-20" and date_add( "2011-08-31" , interval 1 day)
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.

Improve this answer

Comments

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.

Improve this answer

Comments

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'
Improve this answer

6 Comments

I think you meant to include a time (23:59:59) on the corrected query?
Note the DATE() function - this converts the DATETIME to a DATE, with the time component effectively set to 00:00:00.
That query actually returned zero results.
Not sure about MySQL, but in MS SQL, that function in the WHERE clause would mean that it couldn't use any indexes.
I'm not sure why the original query somewhat worked -- shouldn't we be using YYYY-MM-DD?
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.