CODEWITHSUNDEEP
sql
klambiguit
klambiguit

Reputation: 537

Query a range of date

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! :)

Upvotes: 0

Views: 209

Answers (3)

klambiguit
klambiguit

Reputation: 537

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 works now. Just added a time parameter 

SELECT* FROM [tableName] WHERE date >= '20-01-2010' AND date <= '20-02-2010 23:59:59.000'

cheers

Upvotes: 0

Ben
Ben

Reputation: 16533

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.

Upvotes: 0

Francesca
Francesca

Reputation: 21640

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.

Upvotes: 1

Related Questions