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

    Join Date
    Nov 2014
    Posts
    4
    Rep Power
    0

    Query between two dates


    Hi

    Been trying to query a table date column from a form text input to show all rows between two dates using the BETWEEN operator but its not showing the end date. Whatever I try with the syntax shows all but the end date.

    Is there any way to add something like +24 hrs to the end date or to the variable? I have also tried <= >= and nothing seems to work. Seems the between operator is not all inclusive.
  2. #2
  3. No Profile Picture
    Super Moderator
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2009
    Location
    Hartford, WI
    Posts
    1,406
    Rep Power
    24
    If you could provide your current/attempted scripting, we would be able to help a bit more. IF I am correct, BETWEEN is ran rather literally. Say if you ask BETWEEN 5 and 10, it would result with only 4 items. 6,7,8, and 9. If your current script is running as desired other than that very last date, simply add 1 day to the variable before running the query, as a simple/quick fix.
    Last edited by Triple_Nothing; November 23rd, 2014 at 06:57 AM.
    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.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2014
    Posts
    4
    Rep Power
    0
    Yes that would fix it, but I don't know the syntax to add 1 day to the variable. Thanks
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Apr 2009
    Posts
    2,229
    Rep Power
    1231
    Code:
    $date = '2014-11-22';
    $date = date('Y-m-d', strtotime("$date + 1 day"));
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2014
    Posts
    4
    Rep Power
    0
    Thanks dude but not really what I was looking for. I have resolved it though by reassigning the variable similar to what was suggested earlier, I just added ++ in front. This actually added + 1 day to the value!
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2011
    Location
    Sydney Australia
    Posts
    215
    Rep Power
    86
    Originally Posted by Triple_Nothing
    Say if you ask BETWEEN 5 and 10, it would result with only 4 items. 6,7,8, and 9.
    WRONG.
    Read the MySQL manual.
    MySQL :: MySQL 5.6 Reference Manual :: 12.3.2 Comparison Functions and Operators

    ...WHERE id is Between 5 and 10 returns 5, 6, 7, 8 ,9, 10.

    Comments on this post

    • Triple_Nothing agrees : My apologies, hence my "IF I am correct, BETWEEN is ran rather literally"
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Apr 2009
    Posts
    2,229
    Rep Power
    1231
    Thanks dude but not really what I was looking for.
    Don't call me "dude". That's how a child addresses another child. I'm an adult.

    The solution I provided answered your "Is there any way to add something like +24 hrs to the end date or to the variable?" question given that you didn't provide any of your code so I had to guess your code.

    How do I ask a good question? - Help Center - Stack Overflow
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2014
    Posts
    4
    Rep Power
    0
    Yes, my apologies

IMN logo majestic logo threadwatch logo seochat tools logo