SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    Non-Member
    Join Date
    Feb 2005
    Posts
    737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Output all records for this month?

    Hello All,

    Looking for some help. I was hoping to run a query to get all records in a table where the current month is equal to a column called start.

    So for example this month is April. So say in my database I had these records:

    2009-04-01 09:00:00
    2009-03-08 09:00:00
    2009-04-15 09:00:00

    Only the first two records would be outputted. I thought it would be something like this?

    Code:
    SELECT * FROM `table` WHERE (MONTH(starts) = MONTH(GETDATE()))
    Thanks

  2. #2
    Who turned the lights out !! Mandes's Avatar
    Join Date
    May 2005
    Location
    S.W. France
    Posts
    2,496
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Im not sure that GETDATE() is valid MySQL (I think its a MSSQL command)

    Try using NOW()

    Code:
    SELECT * 
      FROM `table` 
        WHERE MONTH(starts) = MONTH(NOW())
    ;
    also your text said you field was called 'start' but you reference 'starts' in the code
    A Little Knowledge Is A Very Dangerous Thing.......
    That Makes Me A Lethal Weapon !!!!!!!!

    Contract PHP Programming

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    using MONTH like that will return all april rows, regardless of the year

    you'd have to use both YEAR and MONTH to get it right

    there's a better, way, however, and this way will also scale, i.e. if there's an index on the date column, the following method will be efficient too --
    Code:
    WHERE starts >= CURRENT_DATE - INTERVAL DAYOFMONTH(CURRENT_DATE) - 1 DAY
      AND starts  < 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
  •