SitePoint Sponsor

User Tag List

Results 1 to 19 of 19
  1. #1
    SitePoint Zealot
    Join Date
    May 2008
    Posts
    108
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SQL date problem

    I have a table of member information which includes an expiration date for each member.

    I need a result set that includes everyone who will expire 2 months from now... however it isn't exactly "2 months from today". Instead, I need it to determine the month that is 2 months from now, then include every member who will expire at some time within that month.

    For example, today is April 18, so I would need a query that says "give me ever member that expires in June". If I ran the query tomorrow it would still get every member expiring in June.

    When I run it again - some time mid May - it should get every member expiring in July.

    My php-programming brain wants to say "get the month number of today (4), add 2 (4+2), then match every expiration date that begins with month 6. But I don't know how to do this in SQL.

    Any suggestions?

  2. #2
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,747
    Mentioned
    65 Post(s)
    Tagged
    0 Thread(s)
    Someone's gonna do this better than me, but...

    Code MySQL:
    SELECT * FROM table 
    WHERE date > CONCAT(YEAR(CURDATE()),"-",MONTH(CURDATE())+2,"-00") 
    AND date < CONCAT(YEAR(CURDATE()),"-",MONTH(CURDATE())+3,"-00")
    Last edited by StarLion; Apr 18, 2011 at 10:50. Reason: Prettify it.

  3. #3
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    4,910
    Mentioned
    96 Post(s)
    Tagged
    0 Thread(s)
    Code SQL:
    SELECT
        *
    FROM
        TABLE
    WHERE
        DATE > NOW() + INTERVAL 2 MONTH
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  4. #4
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,747
    Mentioned
    65 Post(s)
    Tagged
    0 Thread(s)
    Wouldnt that just shove today's date up 2 months, Space?

    His idea was to get everyone from June, not everyone after June 18.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Renkai View Post
    ... determine the month that is 2 months from now, then include every member who will expire at some time within that month.
    i'm gonna give you the long explanation first

    when doing any type of date search, it's important to remember that you'll get a table scan unless you take steps to ensure that the index on the date column can be used

    this means that you want a query that looks similar to starlion's --
    Code:
    WHERE expirydate >= ...
    except that the rest of starlion's code is broken (month+3 in december will give month 15, right?)

    another thing to remember is that it's much better to set an open upper end to a date range

    so for example if you want all dates in a month, this type of construction is problematic --
    Code:
    WHERE expirydate >= '2011-06-01'
      AND expirydate <= '2011-06-30'
    the reason is, of course, that you would have to figure out the last day of the month, which gets messy for february

    furthermore, if your column had datetimes instead of dates, you'd end up doing something like this --
    Code:
    WHERE expirydate >= '2011-06-01 00:00:00'
      AND expirydate <= '2011-06-30 23:59:59'
    which is even messier (and can fail if your database system supports fractions of a second)

    with an open upper end of the range, you would write --
    Code:
    WHERE expirydate >= '2011-06-01'
      AND expirydate  < '2011-07-01'
    the neat part about this solution is that

    1. it allows the index to be used, i.e. it is optimized and efficient, and
    2. no "last day of month" calculation is required, and
    3. it covers datetimes as well as dates

    so with that out of the way, what we need to do for this particular situation is find the first day of the month for the month 2 months ahead of this month, and the first day of the month for the month after that

    with me so far?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Code:
    WHERE expirydate >= CURRENT_DATE - INTERVAL DAYOFMONTH(CURRENT_DATE) - 1 DAY
                                     + INTERVAL 2 MONTH
      AND expirydate  < CURRENT_DATE - INTERVAL DAYOFMONTH(CURRENT_DATE) - 1 DAY
                                     + INTERVAL 3 MONTH
    the only tricky part here is DAYOFMONTH(CURRENT_DATE) - 1

    today's the 18th, so this equates to 17, and if you subtract 17 days from today, you get April 1st

    if you happen to do this on the 1st of any month, then DAYOFMONTH(CURRENT_DATE) gives 1, and you subtract 1 less than that, namely 0, and thus still end up with the 1st of the month as your answer

    the rest of the formula should be obvious

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,747
    Mentioned
    65 Post(s)
    Tagged
    0 Thread(s)
    completely spaced out the +2/+3 looping around... lol. Still, you could use...
    Code MySQL:


    .... right?

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by StarLion View Post
    .... right?
    not really

    i mean, it might work, but only if you factor in the fact that string manipulation is A LOT slower than date arithmetic, and that you're playing with invalid dates there...

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,747
    Mentioned
    65 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    Code:
    WHERE expirydate >= CURRENT_DATE - INTERVAL DAYOFMONTH(CURRENT_DATE) - 1 DAY
                                     + INTERVAL 2 MONTH
      AND expirydate  < CURRENT_DATE - INTERVAL DAYOFMONTH(CURRENT_DATE) - 1 DAY
                                     + INTERVAL 3 MONTH
    the only tricky part here is DAYOFMONTH(CURRENT_DATE) - 1

    today's the 18th, so this equates to 17, and if you subtract 17 days from today, you get April 1st

    if you happen to do this on the 1st of any month, then DAYOFMONTH(CURRENT_DATE) gives 1, and you subtract 1 less than that, namely 0, and thus still end up with the 1st of the month as your answer

    the rest of the formula should be obvious

    I just ran a test on this query, and running it with '2011-06-01' in place of CURRENT_DATE returned a result from 2011-05-31.... are you SURE that -1 on day 1 = day 1, and not last month's last day?

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by StarLion View Post
    running it with '2011-06-01' in place of DAYOFMONTH(CURRENT_DATE) ...
    DAYOFMONTH(CURRENT_DATE) is 18, i.e. an integer, so you can't just stuff '2011-06-01' in there
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Zealot
    Join Date
    May 2008
    Posts
    108
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks so much for all the help. I'm getting CONCAT is not a function and also NOW is not a function.

    r937 - while I get the logic of all your explanations, I don't know what the INTERVAL bit is doing... is this a SQL function? I'm not so good with SQL beyond a select statement. I'm having trouble understanding how the "+ INTERVAL 2 MONTH" works at the end.

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    yeah, sorry, eric, i forgot that this wasn't the mysql forum

    would you mind letting us know which database system you're using?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    if it's SQL Server, you want this --
    Code:
    WHERE expirydate >= DATEADD(mm,DATEDIFF(mm,0,GETDATE())+2,0)
      AND expirydate  < DATEADD(mm,DATEDIFF(mm,0,GETDATE())+3,0)
    GETDATE() returns the current datetime, not date

    the DATEDIFF function counts the number of month boundaries between the current datetime and the "zero date" -- in effect, it strips the time off, as well as ignoring the day of month

    then DATEADD adds back the number of month boundaries

    DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0) gives the first day of the current month

    DATEADD(mm,DATEDIFF(mm,0,GETDATE())+2,0) gives the first day of the month in 2 months
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SitePoint Zealot
    Join Date
    May 2008
    Posts
    108
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    yeah, sorry, eric, i forgot that this wasn't the mysql forum

    would you mind letting us know which database system you're using?
    SQL Server 2005 - sorry I forgot to mention that!

  15. #15
    SitePoint Zealot
    Join Date
    May 2008
    Posts
    108
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    if it's SQL Server, you want this --
    Code:
    WHERE expirydate >= DATEADD(mm,DATEDIFF(mm,0,GETDATE())+2,0)
      AND expirydate  < DATEADD(mm,DATEDIFF(mm,0,GETDATE())+3,0)
    Initial testing is working well with this...

  16. #16
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,747
    Mentioned
    65 Post(s)
    Tagged
    0 Thread(s)
    Out of curiousity, r937 - if it works correctly on day 1 (1 - 1 = 0), why have the -1 day at all? Since the 0'th of the month = 1st of the month?

  17. #17
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by StarLion View Post
    the 0'th of the month = 1st of the month?
    oh, really?
    Attached Images Attached Images
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  18. #18
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,747
    Mentioned
    65 Post(s)
    Tagged
    0 Thread(s)
    Hmm.. okay, I think i see where i'm going wrong..
    When you say
    It gets translated as

    Because in my head, it went..
    Code MySQL:
    CURRENT_DATE //04-01
    - INTERVAL DAYOFMONTH(CURRENT_DATE) //04-00?
    - 1 DAY //Now were at the end of March
    + INTERVAL 2 MONTH // should be the end of May?

    Silly implied order of operations.

  19. #19
    SitePoint Zealot
    Join Date
    May 2008
    Posts
    108
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Rudy and everyone, thanks for the help with this.


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
  •