Web Hosting Forums

Results 1 to 5 of 5

This is a discussion on MYSQL Date Query Question in the Hosting Talk & Chit-chat forum
I'm really struggling with dates in mysql. :-( Could somebody show me how i could edit the following query to show stuff thats out 7 ...

  1. #1
    Loyal Client
    Join Date
    Mar 2007
    Posts
    118

    MYSQL Date Query Question

    I'm really struggling with dates in mysql. :-(

    Could somebody show me how i could edit the following query to show stuff thats out 7 days in the future and then limit that for stuff that's out until the end of that week? Ok that doesn't make sense.

    The query i have shows products released this week...

    What i need the query to do now is, not show stuff that's out this week but next week, and then for only 7 days sorta thing...

    Here is the query i have...

    Code:
    SELECT *, DATE_FORMAT(release_date, '%e-%b-%Y') AS release_date FROM cvg_portal.P_products WHERE release_date<=DATE_ADD(CURDATE(),INTERVAL 30 DAY) AND release_date>=CURDATE()

  2. #2
    Ron
    Ron is offline
    Loyal Client
    Join Date
    Aug 2002
    Posts
    7,503
    Quote Originally Posted by beesfan View Post
    Code:
    SELECT *, DATE_FORMAT(release_date, '%e-%b-%Y') AS release_date FROM cvg_portal.P_products WHERE release_date<=DATE_ADD(CURDATE(),INTERVAL 30 DAY) AND release_date>=CURDATE()
    It's all about the WHERE clause, I'm going to help you to figure it out.

    Figure out how to say in SQL:

    WHERE the release date is greater than or equal to 7 days from now AND the release date is less than or equal to 14 days from now.
    Good luck

  3. #3
    Loyal Client
    Join Date
    Mar 2007
    Posts
    118
    Thanks for the reply Ron, sql dates really aren't my strong point...

    So far i have

    Code:
    WHERE release_date>=CURDATE() + 7 AND release_date<=CURDATE() +7
    That query isn't working, it isn't chucking out any syntax errors but i'm not getting any records returned?

    Any pointers?

  4. #4
    Loyal Client
    Join Date
    Mar 2007
    Posts
    118
    Ahh, i changed the 7 in the <= part to 14 and it's now giving me the records i want. Is that query correct then or would you do it differently?

    Thanks,
    beesfan

  5. #5
    Ron
    Ron is offline
    Loyal Client
    Join Date
    Aug 2002
    Posts
    7,503
    If it works and it does its thing quickly....

    Next questions:
    Does this table have a large amount of data? Does each record have a large amount of columns? Is there an index that has release_date as the first column?
    Good luck

Bookmarks

Posting Permissions

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