Page 1 of 1

querying from and to dates

Posted: Fri Jan 16, 2009 12:08 am
by susrisha
I have a table with a datetime field.
Now i want to select rows between two specific dates.

Lets say from 27-11-08 to 16-01-09. I want the result to display all the entries within that period including the records that are entered on 16-01-09. I could not get a proper mysql query to do that. Can someone help me out with this one?

present code i am using to query is

Code: Select all

 
mysql_query("SELECT * FROM transactions WHERE 
                                Time >'$from_date' 
                                AND 
                                Time<='$to_date' 
                                AND 
                                loginID = $loginID");
 

Re: querying from and to dates

Posted: Fri Jan 16, 2009 12:40 am
by requinix
The dates have to be in YYYY-MM-DD format.

And there's a specific syntax you can use for situations like this:

Code: Select all

SELECT * FROM transactions WHERE Time BETWEEN '$from_date' AND '$to_date' AND loginID = $loginID

Re: querying from and to dates

Posted: Fri Jan 16, 2009 1:24 am
by susrisha
that was helpful but there is another problem. I am unable to get the rows which were inserted ON '$to_date'
The problem is if i give the dates as 2009-01-14 to 2009-01-16, i want it to display all the rows even on 2009-01-16. The solution might be to add a day to the $to_date variable but how to do it?

Re: querying from and to dates

Posted: Fri Jan 16, 2009 1:48 am
by requinix
susrisha wrote:that was helpful but there is another problem. I am unable to get the rows which were inserted ON '$to_date'
BETWEEN...AND includes the "from" and "to" dates.

Is the "Time" field a DATETIME? When you give a date without a time MySQL pretends you said it was at midnight. So 2008-01-15 actually means 2008-01-15 00:00:00.
If you think about it you'll see what happens.

Adding 23:59:59 to the "to" date should fix that.

Code: Select all

SELECT * FROM transactions WHERE Time BETWEEN '$from_date' AND '$to_date 23:59:59' AND loginID = $loginID

Re: querying from and to dates

Posted: Fri Jan 16, 2009 1:52 am
by susrisha
ya i was excluding the time part actually.. Solved the problem. I just have to tell its midnight 11:59 of that day and it works . thanks :lol: