Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    New Coder
    Join Date
    Mar 2006
    Posts
    93
    Thanks
    3
    Thanked 0 Times in 0 Posts

    compare date problem

    In a table, i have two date fields (startDate, endDate)

    I want to make a query that checks if two date that i entered in a form (date1,date2) is between the two dates dates in my table.

    date1 = 2006-08-01
    date2 = 2006-08-31

    I thought something like this would work but it doesnt... can anybidy help me?

    SELECT typeFormation, startDate, endDate FROM formations WHERE (date1>=startDate AND date1 <= endDate) OR (date2>=startDate AND date2 <= endDate)

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    The example you give should work fine. Alternatively, you can use the "between" keyword.

    http://dev.mysql.com/doc/refman/4.1/...operators.html

  • #3
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,633
    Thanks
    4
    Thanked 148 Times in 139 Posts
    Is the date type of your startdate and enddate columns acutally a DATE type? If it isn't but is a varchar or a char then you may not be able to return correct results.

    also have you tried hard coding your values that are being passed from the form just to make sure those are being passed correctly?

  • #4
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    Is the date type of your startdate and enddate columns acutally a DATE type?
    That is a really good question-- could be the whole problem right there. I just assumed the columns are date types.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •