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

    Join Date
    Mar 2007
    Posts
    169
    Rep Power
    37

    Mysql between dates


    How do i run a query for between dates?

    to do ONE date for between is easy i know. but what if i have 2 dates?

    i have a leave program. let say i take of from the 10th to the 15th. I want to run a query from the 7th to the 17th and then that record should appear.

    It is just as if i am telling you that range 12-15 is between 10 and 20.

    Can it be done?
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,055
    Rep Power
    383
    I am not sure I completely understand; but is this what you are looking for?
    Pseudo SQL:

    SQL Code:
    SELECT * FROM TABLE T
    WHERE T.FROM_DATE >= 7th
    AND T.TO_DATE <= 17th


    That will give you all records that fall within the range of interest.

    If that is not what you want, could you try another explanation?
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2007
    Posts
    169
    Rep Power
    37

    re


    Hi Clive

    Lets take your example. Let say i take leave from the 2nd to the 15. Then that query of your would not pick me up. But i AM on leave in those periods.

    It will fail on the WHERE clause the AND is fine. I would like to know who is on leave in between to periods.

    You understand?Thanks again.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,055
    Rep Power
    383
    OK. I understand better now.

    Will this work?

    SQL Code:
    SELECT * FROM TABLE T
        WHERE T.FROM_DATE <= 17th
        AND T.TO_DATE >= 7th
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2008
    Posts
    344
    Rep Power
    6
    I think what you want is to capture if either the from_date or the to_date are within your range,

    select from_date, to_date from Table T
    where
    (T.From_Date between :startdate and :enddate) or
    (T.To_Date between :startdate and :enddate)

    use the query's parambyname property to set the startdate and enddate parameters.

    This will include the record if either date is between the range, so if someone starts their leave on the end date, or if the leave ends on the start date, or if the leave is completely within the period, it will be selected.
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,055
    Rep Power
    383
    Originally Posted by majlumbo
    I think what you want is to capture if either the from_date or the to_date are within your range,

    select from_date, to_date from Table T
    where
    (T.From_Date between :startdate and :enddate) or
    (T.To_Date between :startdate and :enddate)
    I agree that this looks correct.

    My second suggestion should have used an OR not AND and would give the same
    result I think; but this is a much clearer expression of the intent of the query
    and therefore superior.

    Clive
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2008
    Posts
    344
    Rep Power
    6
    Originally Posted by clivew
    I agree that this looks correct.

    My second suggestion should have used an OR not AND and would give the same
    result I think; but this is a much clearer expression of the intent of the query
    and therefore superior.

    Clive
    Actually, my query does not solve the problem. If someone starts their leave before the start date, and ends it after the end date, then it wouldn't be selected even thought they were on leave during the period.

    This should work however...

    Code:
    select from_date, to_date from Table T
    where
    (T.From_Date between :startdate and :enddate) or
    (T.To_Date between :startdate and :enddate) or
    (T.From_Date < :startdate and T.To_Date > :enddate)

    Comments on this post

    • clivew agrees : Good catch. I was way too simple.
    Last edited by majlumbo; July 12th, 2012 at 04:29 PM.
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2007
    Posts
    169
    Rep Power
    37

    re


    Thanks guys. i do not know why i did not think of trying 2 between statements. but i would not have think of the last statement. appreciate all your help.
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,055
    Rep Power
    383
    Our pleasure.

    I have been thinking about my simple query and I am not sure that it is wrong.
    If you have time to test it against majlumbo's query to see if it yields the same results
    or anyone else can explain where it would fail I would be grateful.
    Here it is again (using parameters this time)
    Code:
    SELECT * FROM TABLE T
            WHERE T.FROM_DATE <= :enddate
            AND T.TO_DATE >= :startdate
    Clive
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2008
    Posts
    344
    Rep Power
    6
    This may just work...
  20. #11
  21. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2007
    Posts
    169
    Rep Power
    37

    Re


    Hallo Clive

    Your query might not work if i start my leave after the fromdate?

    lets say i take leave from the 19th to the 21th.

    I create a report from the 17th to the 25. To see whom in that period take leave...i would not be on the list... my STARTDATE is AFTER the :fromdate but my ENDDATE is smaller than my :todate.

    am i right in saying that? Then majlumbo's code will work?

    Thanks you guys so much for your help!!!
  22. #12
  23. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,055
    Rep Power
    383
    Your query might not work if i start my leave after the fromdate?
    lets say i take leave from the 19th to the 21th.
    I create a report from the 17th to the 25.

    SQL Code:
    SELECT * FROM TABLE T
    WHERE T.FROM_DATE <= :enddate
    AND T.TO_DATE >= :startdate


    Yes it will.
    Taking your dates:
    T.FROM_DATE is 19th.
    T.TO_DATE is 21st.
    :enddate is 25th.
    :startdate is 17th.

    19 is less than 25
    21 is greater than 17

    Therefore both conditions are true and the record will be included.

    Clive

IMN logo majestic logo threadwatch logo seochat tools logo