SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Enthusiast
    Join Date
    Jun 2006
    Posts
    65
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Displaying records by date

    Morning,

    I have an SQL statement that includes a Date field from my database.

    I would like to add a where clause to the statement so that it only shows the records that have todays date in the date field, another statement that picks out any records in the next seven days and a third that picks out all records within the next 30 days.

    Could anyone tell me how to do this?

    I have had a go using the following but it doesnt work!!

    WHERE DATE_SUB(CURDATE(),INTERVAL 7 DAY) <= date

    Many Thanks

  2. #2
    SitePoint Addict
    Join Date
    Apr 2004
    Location
    Belgian in Mexico
    Posts
    307
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Maybe:
    WHERE DATEDIFF(date,CURDATE()) BETWEEN 0 AND 7
    MichaŽl Niessen
    http://assemblysys.com
    (Countries/states/cities with latitude & longitude,
    weathercodes & topical databases)

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    put your column name all by itself on the left side of the operator, and all the calculations on the right
    Code:
    WHERE `Date` = CURRENT_DATE
    Code:
    WHERE `Date` >= CURRENT_DATE
      AND `Date`  < CURRENT_DATE + INTERVAL 7 DAY
    Code:
    WHERE `Date` >= CURRENT_DATE
      AND `Date`  < CURRENT_DATE + INTERVAL 30 DAY
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Enthusiast
    Join Date
    Jun 2006
    Posts
    65
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for these, both options work so are their any arguments as to which meathod is the best to use?

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    if you apply a function to a column, then the database will not use an index on that column, meaning a table scan

    if you put the functions and calculations on the right side of the operator, then the database can calculate a value (e.g. today + 7 days) and use that for an indexed search
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    rudy's is better because it can utilize an index, whereas micha&#235;l's can not.
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast


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
  •