mysql search between dates

[Anon]

Hi,

I want to select all rows where the value of the date column (fecha) is between to dates

the date column is set up like this: yyyy-mm-dd

I tried several mysql query's, for example:

SELECT * FROM table WHERE fecha >= $date1 AND fecha <= $date2

It keeps on giving me 0 results while there should be results.

What is the correct syntax to search on dates?

Thanks,
Raoul

[Anon]

A quick way to do it is convert the dates in timestamp format. Then, your test is between numbers.

[Anon]

I have been using such type of query with out any problem.

Select * from persons where (DateOfBirth >='$date1') and (DateOfBirth <= '$date2');

I think the concept is the same.

[van__]

Use the UNIX_TIMESTAMP() SQL function.

$sql = "SELECT * FROM table WHERE (UNIX_TIMESTAMP(fecha) >= UNIX_TIMESTAMP($date1)) AND (UNIX_TIMESTAMP(fecha) <= UNIX_TIMESTAMP($date2))";

Also handy is:
DATE_FORMAT('%FORMAT',$DATE)
DATE();
NOW() <-great for inserting the date/time for right now for any tstamp field
YEAR()
MINUTES()
SECONDS()
MONTH()
...
and so on and so forth.

Check out http://www.mysql.org for everything you could ever want to know about their usage of ANSI SQL

[Anon]

Thanks guys,

Apparantly I have to put $date1 and $date2 between '', then it does work fine.

I will check out the UNIX timestamp too!

Raoul