Using MySql between clause with dates

Question

I have always had a problem with this, for clarification purposes, when using mysql between clause, does it include the parameters or only the values that fall between them, for example:

where date between '2013-06-01' and '2013-06-06'

will this above statement include the values with a date of 2013-06-01 as well or only from '2013-06-02', and what happens if the statement stays as is, but then the date values have hours in them, will MySql automatically hours to this statement

Answer 1

Fabio is actually not right, if hours, minutes and seconds will be included this

where date >= '2013-06-01' and date <= '2013-06-06'

becomes internally

where date >= '2013-06-01 00:00:00' and date <= '2013-06-06 00:00:00'

So you actually just select 1 second of 2013-06-06, not the whole day!

Same with BETWEEN of course. To get the whole day of 2013-06-06 you'd have to write

where date >= '2013-06-01' and date <= '2013-06-06 23:59:59'

or

where date BETWEEN '2013-06-01' AND '2013-06-06 23:59:59'

Go ahead, try it yourself (or see it live in an sqlfiddle):

create table foo (my_date date, my_timestamp timestamp, my_datetime datetime);
insert into foo values ('2013-06-06', '2013-06-06 12:23:34', '2013-06-06 13:35:48');

select * from foo
where
my_date <= '2013-06-06'; /*returns row*/

select * from foo
where
my_timestamp <= '2013-06-06'; /*does NOT return row*/

select * from foo
where
my_datetime <= '2013-06-06'; /*does NOT return row*/

select * from foo
where
my_timestamp <= '2013-06-06 23:59:59';  /*returns row*/

select * from foo
where
my_datetime <= '2013-06-06 23:59:59';  /*returns row*/