#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Posts
    24
    Rep Power
    0

    Date Range Parameter in Query


    Hi all.

    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!

    Code:
     $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) '
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jun 2009
    Posts
    667
    Rep Power
    6
    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 and knows not he knows not: he is a fool - shun him. He who knows not and knows he knows not: he is simple - teach him. He who knows and knows not he knows: he is asleep - wake him. He who knows and knows he knows: he is wise - follow him
  4. #3
  5. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,914
    Rep Power
    1045
    The manual exists for a reason. DATE().
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers? There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Posts
    24
    Rep Power
    0
    Thank you both!

IMN logo majestic logo threadwatch logo seochat tools logo