Thread: stupid dates!

    #1
  1. Plays with fire
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Aug 2003
    Location
    Barsoom
    Posts
    1,102
    Rep Power
    143

    stupid dates!


    I never seem to get this right. I need to select a group of classes for the next month if today's date is after the 23rd.

    Maybe the leap year is hosing this up, but for now, this only returns data through March 30, not March 31:

    ...WHERE date_time
    BETWEEN NOW()
    AND LAST_DAY(DATE_ADD(CURDATE(), INTERVAL 1 MONTH))
    ORDER BY date_time
    I'm seriously confused. Being the 27th, shouldn't this pull everything from Feb 27 - March 31? My database does have records for March 31 that should be displayed.

    Please help!
    Last edited by Frank Grimes; February 27th, 2016 at 08:21 PM.
    “Be ashamed to die until you have won some victory for humanity.” -- Horace Mann

    "...all men are created equal." -- US Declaration of Independence
  2. #2
  3. No Profile Picture
    Super Moderator
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2009
    Location
    Hartford, WI
    Posts
    1,399
    Rep Power
    24
    What kind of columns are those that hold these dates you wish to compare?
    He who knows not that he knows not is a fool, ignore him. He who knows that he knows not is ignorant, teach him. He who knows not that he knows is asleep, awaken him. He who knows that he knows is a leader, follow him.
  4. #3
  5. Plays with fire
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Aug 2003
    Location
    Barsoom
    Posts
    1,102
    Rep Power
    143
    Originally Posted by Triple_Nothing
    What kind of columns are those that hold these dates you wish to compare?
    Date_time is a datetime field.
    “Be ashamed to die until you have won some victory for humanity.” -- Horace Mann

    "...all men are created equal." -- US Declaration of Independence
  6. #4
  7. No Profile Picture
    Super Moderator
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2009
    Location
    Hartford, WI
    Posts
    1,399
    Rep Power
    24
    Make sure the 2nd item isn't giving just the date, since it might process as '2016-03-31 00:00:00' instead of '2016-03-31 23:59:59'.
    He who knows not that he knows not is a fool, ignore him. He who knows that he knows not is ignorant, teach him. He who knows not that he knows is asleep, awaken him. He who knows that he knows is a leader, follow him.
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,656
    Rep Power
    4288
    Code:
    WHERE date_time >= NOW()
      AND date_time  < LAST_DAY(CURRENT_DATE + INTERVAL 1 MONTH) 
                         + INTERVAL 1 DAY
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  10. #6
  11. No Profile Picture
    Super Moderator
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2009
    Location
    Hartford, WI
    Posts
    1,399
    Rep Power
    24
    Will using the NOW() for either instance grab all desired items from the first day? Or if processed at noon, will it only grab items from the 2nd half of that day? Are items from the first half desired?
    He who knows not that he knows not is a fool, ignore him. He who knows that he knows not is ignorant, teach him. He who knows not that he knows is asleep, awaken him. He who knows that he knows is a leader, follow him.
  12. #7
  13. Plays with fire
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Aug 2003
    Location
    Barsoom
    Posts
    1,102
    Rep Power
    143
    Originally Posted by r937
    Code:
    WHERE date_time >= NOW()
      AND date_time  < LAST_DAY(CURRENT_DATE + INTERVAL 1 MONTH) 
                         + INTERVAL 1 DAY
    All at once it comes into focus. Hahaha....

    I'm using BETWEEN which won't include the last day. Really wish I could buy you lunch for all your help.

    Thank you!
    “Be ashamed to die until you have won some victory for humanity.” -- Horace Mann

    "...all men are created equal." -- US Declaration of Independence
  14. #8
  15. Plays with fire
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Aug 2003
    Location
    Barsoom
    Posts
    1,102
    Rep Power
    143
    Originally Posted by Triple_Nothing
    Will using the NOW() for either instance grab all desired items from the first day? Or if processed at noon, will it only grab items from the 2nd half of that day? Are items from the first half desired?
    That's exactly what I need. I want to show only available items so clients won't schedule for classes in the past.
    “Be ashamed to die until you have won some victory for humanity.” -- Horace Mann

    "...all men are created equal." -- US Declaration of Independence
  16. #9
  17. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,656
    Rep Power
    4288
    Originally Posted by Frank Grimes
    I'm using BETWEEN which won't include the last day.
    well, it could, but you'd have to fudge the boundary a bit (which is why i never use it with dates and datetimes) --
    Code:
    WHERE date_time BETWEEN NOW()
                        AND LAST_DAY(CURRENT_DATE + INTERVAL 1 MONTH) 
                            + INTERVAL 24 HOUR
                            - INTERVAL 1 SECOND
    Originally Posted by Frank Grimes
    Really wish I could buy you lunch for all your help.
    just pay it forward... help someone else with whatever you're good at

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  18. #10
  19. Plays with fire
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Aug 2003
    Location
    Barsoom
    Posts
    1,102
    Rep Power
    143
    Originally Posted by r937
    just pay it forward... help someone else with whatever you're good at
    That used to be Flash until...sigh...

    Comments on this post

    • r937 agrees
    “Be ashamed to die until you have won some victory for humanity.” -- Horace Mann

    "...all men are created equal." -- US Declaration of Independence

IMN logo majestic logo threadwatch logo seochat tools logo