SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    date manipulation question

    Hi,

    the date is stored as 2011-02-04

    I want to retrieve a record (just one) if that date falls within this month - which it does.

    is this AND clause correct or have a I fluked it?

    Code MySQL:
    and MONTH(fs.live_from) = month(curdate() )


    bazz

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    it's not efficient, but at least it's correct, i.e. returns the correct results
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Just realised, I need to check the month_year against the fs.live_from.

    So, how would I change my (poor), effort to do it properly and efficiently?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    is there a front-end language that could be used to generate the endpoints of the date range?

    for efficiency, you want this format --
    Code:
    WHERE fs.live_from >= '2011-02-01'
      AND fs.live_from  < '2011-03-01'
    by using the first of the following month as the upper end, combined with a "less than" comparison, you don't have to figure out whether you have feb 29th or not ...

    you could still construct those dates in the query itself, using several date calculations based on CURRENT_MONTH, but it's easier if you do it in the front-end app and just pass in the two dates
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks rudy,

    I fumbled about - bit foggy at the mo - and went with 1st and 31st of the month. whatever date (28th or 29th etc) will always be <= 31st.

    Is there much difference in efficiency terms, between using BETWEEN and using <= and >= ?

    The docs are showing 'Service Temporarily Unavailable', which is why I ask here instead of looking it up.

    bazz

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by IBazz View Post
    ... will always be <= 31st.
    but only if you're running in ALLOW_INVALID_DATES mode (which you shouldn't), otherwise it won't even let you say '2011-02-31'

    also, if you're testing a datetime column (as opposed to a date column), then you will miss all the times of the day after midnight on the 31st

    there is no performance difference between BETWEEN and the >= and <= (or <) conditions, it's just that correct results are a lot easier to obtain with the simple approach i suggested
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK, rudy thanks again.

    I'll build the exact end date in perl, though its fiddly when getting 1st jan, during december.

    bazz

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by IBazz View Post
    ... though its fiddly
    can't be any worse than the sql equivalent...
    Code:
    WHERE fs.live_from >= CURRENT_DATE - INTERVAL DAYOFMONTH(CURRENT_DATE)-1 DAY
      AND fs.live_from  < CURRENT_DATE - INTERVAL DAYOFMONTH(CURRENT_DATE)-1 DAY
                                       + INTERVAL 1 MONTH
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •