How to search between two dates using mysql or php

[searchacar]

Hello,

I am doing some search in which my requirment is to search between specific dates.
Like user can put start date and then end date. Once submit then it should find result between these two dates.

Select from table where date1>2001-01-05 And date2< 2001-02-05.

Will this above statement will work. Is there any function which subtracts date like this. How mysql function works when we use query like date less than 2001-01-05

thanks

[nav33n]

I have not checked your query. But this will work.

$query="select * from table where date_column between "2001-01-05" and "2001-01-10";

This will print all the records where the date_column value lies between the first date and the second date.

[johnsquibb]

when in doubt check out the mysql manual...

http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html

[nav33n]

when in doubt check out the mysql manual...

http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html

umm..nowhere in the manual says how to fetch only those records which lies between 2 dates ! :S

[nav33n]

Okay! I checked this and even this works.
Select * from tables where CREATE_TIME > "2008-02-01" and CREATE_TIME < "2008-02-08"

Cheers,
Naveen

[searchacar]

thanks mate. I think this query will work.

[nav33n]

You are welcome! :)
Btw, If your problem is solved, you can mark this thread as solved.

[modesignz]

What if I want to query between a date in August and a date in September. I've tried this and it doesn't work:

$query = "SELECT Date FROM test WHERE Date >= '28/08/2010' AND Date <= '02/09/2010'";

Thanks in advance

[garryamin]

If you want to search two dates between php or mysql then there are so many advance facility will be available for searches the dates in php that can provide so many advance facility it into them.

[Steelcrusher]

SELECT NOW() BETWEEN '1900-1-1' AND '2011-1-1' return 0
SELECT NOW() BETWEEN '1900-1-1' AND '2030-1-1' return 1

http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html

[Steelcrusher]

What if I want to query between a date in August and a date in September. I've tried this and it doesn't work:

$query = "SELECT Date FROM test WHERE Date >= '28/08/2010' AND Date <= '02/09/2010'";

Thanks in advance

from what I know Mysql uses the year-month-day format try sending :

$query = "SELECT Date FROM test WHERE Date >= '2010/08/28' AND Date <= '2010/09/02'";

I tried "SELECT NOW()>= '2012/01/28' AND NOW() <= '2012/03/02'" now was equal to 2012-02-08 10:59:43 and it returned 1