SitePoint Sponsor

User Tag List

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

    Count back from todays date

    I am using dreamweaver to do this so please be gentle!!

    I am producing an online booking system and the customer wishes to look back through booked jobs so yesterday, last 7 days, last month, last 6 month.

    I have managed to create the query for tomorrow, next seven days etc and thought it would be as simple as putting a minus sign in front of the required number of days but it doesnt work.

    Example of the query used

    SELECT booking_id, DATE_FORMAT(date, '%W %D %M, %Y') AS date, name, address, postcode, tel, alt_tel, email, make, model, colour, registration, job_details, parts, comments, installer
    FROM jobs
    WHERE DATEDIFF(date,CURDATE()) BETWEEN 0 AND -180
    ORDER BY jobs.`date`

    Can anyone tell me the correct way to do this please?

    Regards

  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)
    the correct way is to change this --

    ... WHERE DATEDIFF(date,CURDATE()) BETWEEN 0 AND -180

    to this --

    ... WHERE `date` > CURRENT_DATE - INTERVAL 6 MONTH

    the idea is to put the column on one side of the operator, and do the calculations on the other side

    this will allow for indexed retrieval
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Jun 2006
    Posts
    65
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for that, could you also tell me what the operator would be for last 7 days and yesterday?

  4. #4
    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)
    last seven days --

    ... WHERE `date` > CURRENT_DATE - INTERVAL 7 DAY


    yesterday --

    ... WHERE `date` > CURRENT_DATE - INTERVAL 1 DAY


    can you take it from here?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    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)
    oh wait...

    that last one was since yesterday

    if you want only yesterday, that would be --

    ... WHERE `date` > CURRENT_DATE - INTERVAL 1 DAY AND `date` < CURRENT_DATE

    also you may want to use >= and <= instead of > and <

    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
  •