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 6 of 6
  1. #1
    New Coder
    Join Date
    Dec 2012
    Posts
    54
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Specific Date Ranges

    I'm trying to run a few queries that will end up showing me data that has dates of today, 3 days from today, 5 days from today, and so on.

    Right now my range code looks like this:
    Code:
    WHERE dateends BETWEEN curdate() and curdate() + interval 3

    Not sure that the code is right....when I use
    Code:
    WHERE dateends <= CURDATE()
    It seems to be showing me everything, regardless of the date.
    The dateends field is in DATE format.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,869
    Thanks
    79
    Thanked 4,421 Times in 4,386 Posts
    Does this mean you do *NOT* want to see records from tomorrow, 2 days from today, 4 days from today, etc.??

    That is, you only want the "ODD" days?

    ********
    This condition:
    Code:
    WHERE dateends <= CURDATE()
    *should* only show you records where dateends is prior to today.

    When you say dataends is in "DATE format", do you mean it *IS* a DATE field (that is, it has a DATE data type) or just that it is a VARCHAR field in some text format?
    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 Coder
    Join Date
    Dec 2012
    Posts
    54
    Thanks
    12
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    Does this mean you do *NOT* want to see records from tomorrow, 2 days from today, 4 days from today, etc.??

    That is, you only want the "ODD" days?

    ********
    This condition:
    Code:
    WHERE dateends <= CURDATE()
    *should* only show you records where dateends is prior to today.

    When you say dataends is in "DATE format", do you mean it *IS* a DATE field (that is, it has a DATE data type) or just that it is a VARCHAR field in some text format?
    It is simple, yet complicated.

    I want to show groups of records. The dateends field is a DATE field. I want to show dates that equal today only, then I want to see dates that are 3 days in the future until 1 day in the future. Then I want to see dates that are 5 days in the future to 4 days in the future....basically I want to see into the future from the DATE field, but when I go to the next one, I don't want to show what I've already shown, such as things that have todays date showing on the 3 days from today search.

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,869
    Thanks
    79
    Thanked 4,421 Times in 4,386 Posts
    So today is May 13th.

    You would like to see all the records in this order (purposely showing some dates with multiple records):
    Code:
    May 13
    May 13
    May 13
    
    May 15
    May 15
    May 14
    
    May 17
    May 16
    May 16
    May 16
    
    May 19
    May 19
    May 18
    ... etc. ...
    All in one query? All those dates?
    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 Coder
    Join Date
    Dec 2012
    Posts
    54
    Thanks
    12
    Thanked 0 Times in 0 Posts
    That is what I want, but not all in 1 query...I am going to put each date range as its own entry so I would need a query for today, 3 days, 5 days and 7 days...going to use it for a giveaway expiration so I'm going to be using PHP to display the results of each query, just couldn't nail down the sql query.

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,869
    Thanks
    79
    Thanked 4,421 Times in 4,386 Posts
    Okay, then yes, something like:
    Code:
    WHERE dateends 
        BETWEEN DATE_ADD( CURDATE(), INTERVAL 2 DAY )
        AND DATE_ADD( CURDATE(), INTERVAL 3 DAY )
    And then you could just use PHP code to change the 2 and 3 there to whatever day number range you wanted.

    http://dev.mysql.com/doc/refman/5.5/...ction_date-add
    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.


  •  

    Posting Permissions

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