SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Evangelist achintya's Avatar
    Join Date
    Apr 2005
    Location
    Chandannagar(India)
    Posts
    459
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    records few days older

    Hi all,
    I want to extract those values whose modificatio dates are few days older using the following sql
    [SQL]
    select status from note where mdate<=date_add(date('Y-m-d'),interval -2 day) and status='Selected' and id=$r[0];
    [/SQL]
    but the comparison 'mdate<=date_add(date('Y-m-d'),interval -2 day)' doing nothing. I have checked it in db. There is no error too. How to correct that?

  2. #2
    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)
    Code:
    select status
    from note
    where
      mdate <= date_sub(curdate(),interval 2 days)
      and status='Selected'
      and id={$r[0]}
    FYI, when embeding arrays in a string using PHP, you should use curly brackets. you don't always need them, but if you get in the habit of doing it for all of them, you will get fewer bugs.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    the reason your query ran but gave no error message is because date('Y-m-d') is an invalid date, for which mysql, always trying to help us poor human users, decided to substitute 0000-00-00

    and of course when you subtract 2 days from 0000-00-00 you get another invalid date, for which mysql, always trying to help, decided to substitute 0000-00-00

    and since you probably had no rows with mdate <= 0000-00-00, that's why you got no results

    doesn't that make you feel all warm and toasty about mysql?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    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)
    more like moist and squishy. eww....

  5. #5
    SitePoint Evangelist achintya's Avatar
    Join Date
    Apr 2005
    Location
    Chandannagar(India)
    Posts
    459
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    thanks guys,it's working. One more thing can someone tell me what exactly date_sub() does? I want make my knowledge more solid.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by achintya
    ...can someone tell me what exactly date_sub() does? I want make my knowledge more solid.
    it allows you to subtract an interval from a date or datetime value
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Evangelist achintya's Avatar
    Join Date
    Apr 2005
    Location
    Chandannagar(India)
    Posts
    459
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    thanks again.


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
  •