October 30th, 2013, 09:56 AM
Date Range Parameter in Query
I have the following query in my php file that uses the MySQL table's CreateDate field. This CreateDate field has the following sample data: 2013-10-23 21:41:50, 2013-10-23 20:10:05, 2013-10-24 19:18:24.
My query's date parameter is like this: 2013-10-23, 2013-10-24 (basically Y-m-d without the time stamp).
What can I do to my query to only look at the date portion of the CreateDate field. Currently if my parameter is between 2013-10-23 and 2013-10-23, no records are displayed. When I change my parameters to 2013-10-23 and 2013-10-24 then I see records, but I see records for 2013-10-24 when all I wanted was only the records for 2013-10-23.
Thank you in advance!
$query ='SELECT Concat(x.firstname ," " ,x.lastname) as Name, sum(x.Slab) as Slab, SUM(x.DriedIn) as DriedIn, Sum(x.Slab) + Sum(x.DriedIn) as Total from (SELECT firstname, lastname, count(1) as Slab, 0 as DriedIn FROM Users INNER JOIN FieldSuperDataEntry ON Users.userid = FieldSuperDataEntry.UserID Where Type = 1 AND (CreateDate Between :reportdate1 AND :reportdate2) group by firstname, lastname union all SELECT firstname, lastname, 0 as Slab, count(1) as DriedIn FROM Users INNER JOIN FieldSuperDataEntry ON Users.userid = FieldSuperDataEntry.UserID Where Type = 2 AND (CreateDate Between :reportdate3 AND :reportdate4) group by firstname, lastname) x Group by Concat(x.firstname ," " ,x.lastname) order by Concat(x.firstname ," " ,x.lastname) '
October 30th, 2013, 10:18 AM
Well, you are kinda wanting to do something and not say it. Like if I asked you to grab me all the reports submitted this week, Monday THOUGH Wednesday, I'm assuming you WILL grab ones from Wednesday, correct? That is what you are telling this to do in the query. The meerly date w/o a time starts at 00:00:00 in the morning, and ends 12:59:59 at night. So if you tell it a date, it will grab them.
If this is a built date from something on a page, usually what I would do is meerly take the variable holding the end date and just tell it to subtract 1 day from it before entering it into the query.
He who knows not that he knows not is a fool, ignore him. He who knows that he knows not is ignorant, teach him. He who knows not that he knows is asleep, awaken him. He who knows that he knows is a leader, follow him.
October 30th, 2013, 11:18 AM
The manual exists for a reason. DATE().
October 30th, 2013, 02:34 PM