SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    PHP Guru lampcms.com's Avatar
    Join Date
    Jan 2009
    Posts
    921
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question about date function

    Hello!

    I have a table that stores articles with columns article, article_date
    the article_date is in the date format 0000-00-00

    What is the best way to select all articles that are NOT from the current month?


    Thank you.
    My project: Open source Q&A
    (similar to StackOverflow)
    powered by php+MongoDB
    Source on github, collaborators welcome!

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you will get a good discussion about which approach is "best"

    this one is simple and correct, but might not be efficient

    Code:
    WHERE EXTRACT(YEAR_MONTH FROM article_date)
        = EXTRACT(YEAR_MONTH FROM CURRENT_DATE)
    most efficient would be if you can generate the date boundaries in some front end language like php --
    Code:
    WHERE article_date  < '2009-10-01'
       OR article_date >= '2009-11-01'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    PHP Guru lampcms.com's Avatar
    Join Date
    Jan 2009
    Posts
    921
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK, if I can generate the year-month string with php, then I was thinking of this sql:

    WHERE article date NOT LIKE '2009-09&#37;'

    I think this will work best. Since the LIKE condition does not start with %, the mysql will be able to use the index, so it will get result fast.
    My project: Open source Q&A
    (similar to StackOverflow)
    powered by php+MongoDB
    Source on github, collaborators welcome!

  4. #4
    SitePoint Enthusiast freezea's Avatar
    Join Date
    Apr 2009
    Posts
    38
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi.
    In my opinion, maybe you can have a try of RAQ Report. As I know, it provides a complete set of date time functions.
    Regards,
    freezea.
    RAQ Report: Web-based Excel-like Java reporting tool.


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
  •