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 5 of 5
  1. #1
    New to the CF scene
    Join Date
    Aug 2013
    Posts
    9
    Thanks
    1
    Thanked 0 Times in 0 Posts

    mySQL query : working with INTERVAL and CURDATE

    Hello,
    I would like to have an overview what happened 10 months back from today-end of the month, a $query from my table where my

    tableentry + INTERVAL 10 MONTH = curdate() + INTERVAL 30 DAY

    Somehow I'm not able to get a correct result. Does anyone has an idea where the issue is or do I need to write a way around it? Any help welcome and many thanks in advance.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,023
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    Your wording is confusing.

    Do you mean you want:
    (a) All records starting 10 months before the end of the current month.
    (So today is September 1st, the end of this month is September 30th. 10 months back from that is November 30th, 2012. So you want all records from November 30, 2012, until now.)

    *OR*

    (b) All records between 10 months back from today and one month after today.
    (So 10 months back from today is November 1, 2012. On month after today is October 1, 2013. So you want all records from November 1, 2012, to October 1, 2013.

    Which of those do you mean? If you don't mean either one, then give an *EXAMPLE* of what you mean, using today as a starting point.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    New to the CF scene
    Join Date
    Aug 2013
    Posts
    9
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks, I hope this will make it more clear:

    I want all records for the whole month, 10 months back from today
    i.e.
    all records between November 1st, 2012 to November 30th, 2012

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,023
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    AHHH!!! I would NEVER have guessed that is what you meant!

    Okay...

    Code:
    SELECT * FROM yourtable 
    WHERE YEAR(tableentrydate) = YEAR( DATE_SUB( curdate(), INTERVAL 10 MONTH ) )
      AND MONTH(tableentrydate) = MONTH( DATE_SUB( curdate(), INTERVAL 10 MONTH ) ) 
    ORDER BY tableentrydate
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    New to the CF scene
    Join Date
    Aug 2013
    Posts
    9
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I apologize for the confusion, but finally we got it

    Many thanks for the script, works perfect and is what I wanted.


  •  

    Posting Permissions

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