querying from and to dates

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
susrisha
Forum Contributor
Posts: 439
Joined: Thu Aug 07, 2008 11:43 pm
Location: Hyderabad India

querying from and to dates

Post 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");
 
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: querying from and to dates

Post 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
User avatar
susrisha
Forum Contributor
Posts: 439
Joined: Thu Aug 07, 2008 11:43 pm
Location: Hyderabad India

Re: querying from and to dates

Post 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?
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: querying from and to dates

Post 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
Last edited by requinix on Fri Jan 16, 2009 2:06 am, edited 1 time in total.
User avatar
susrisha
Forum Contributor
Posts: 439
Joined: Thu Aug 07, 2008 11:43 pm
Location: Hyderabad India

Re: querying from and to dates

Post 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:
Post Reply