Query a range of date

Question

Hello Guys,

Im trying to query a sort of from - to date. e.g. 20-01-2010 to 20-02-2010. this should include the mentioned dates.

i've tried the following queries but none works.

select * 
  from [tableName] 
 where date >= '20-01-2010' 
   AND date <= '20-02-2010'

but the date where date is equal to 20-02-2010 does not show. i don't know why.

---

select * 
  from [tableName] 
 where date between '20-01-2010' 
            AND '20-02-2010' 

the mentioned dates is not included in the results. i want it to be included in the results.

please help.

thanks in advance! :)

Answer 1

Be sure you don't have datetime values in your table where the time value is not 0. in that case none would be less or equal compared to the upper date you provide.

Answer 2

SELECT date
FROM  `test_table` 
WHERE date >=  '2010-04-01'
AND date <=  '2010-04-30';

Actually works in mysql. Remember that the standard structure of a DATE type field is YYYY-MM-DD.

Answer 3

Hello,

Thanks for all the replies. I figured out the problem.

The reason why the query below doesn't work is because the value of the date field has hh:mm:ss since it's datetime. If you compare to ordinary date, the time part will be 0. That's why data w/ date 20-02-2010 doesn't show up in my result set since the data has hh:mm:ss not equal to 00:00:00.000.

select * from [tableName] where date >= '20-01-2010' AND date <= '20-02-2010'

The query below qorks now. just added a time parameter

select * from [tableName] where date >= '20-01-2010' AND date <= '20-02-2010 23:59:59.000'

cheers