SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Evangelist
    Join Date
    Mar 2011
    Location
    Bellingham, WA
    Posts
    450
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Finding the difference of 2 dates: How tough could it be?

    Hello,

    This is my first time using DateDiff and it looks pretty straightforward; I fear, however, that I keep getting the following error:

    Incorrect parameter count in the call to native function 'DATEDIFF'

    My goal in the code below is to get all records that are more than 3 days old; the Timestamp variable looks like: 2011-05-22 20:26:42

    Code MySQL:
    SELECT * FROM `homepage_homeschool_schedule` WHERE DATEDIFF(DD,Timestamp,CURRENT_TIMESTAMP)>3

    Any help would be appreciated.

    -Eric

  2. #2
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    73 Post(s)
    Tagged
    0 Thread(s)
    Are you using MySQL? (I ask because you wrapped your code in mySQL formatting forum-code) If so, you do have the wrong parameter count.

    MySQL :: MySQL 5.1 Reference Manual :: 11.7 Date and Time Functions

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    best advice ever: in order to allow an index on the column to be used, and therefore to make your query perform well, it is important never to apply a function to a column

    so your DATEDIFF approach is dead right out of the starting gate

    a wise man once said that it is far better to get the correct answer after a while than a wrong answer immediately...

    and to be sure, DATEDIFF can give you the right answer, but it's not efficient

    if you're trying to find "more than 3 days old" you should do it this way --
    Code:
    WHERE `timestamp` < CURRENT_DATE - INTERVAL 3 DAY
    note that you shouldn't use a reserverd word like TIMESTAMP for the name of a column
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Evangelist
    Join Date
    Mar 2011
    Location
    Bellingham, WA
    Posts
    450
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    SELECT *
    FROM homepage_homeschool_schedule
    WHERE dbTime > (SELECT CURRENT_TIME - INTERVAL 3 DAY)

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by kreut View Post
    SELECT *
    FROM homepage_homeschool_schedule
    WHERE dbTime > (SELECT CURRENT_TIME - INTERVAL 3 DAY)
    no, the other way around, and without the parentheses, and using CURRENT_DATE instead, and without the extra SELECT keyword

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

  6. #6
    SitePoint Evangelist
    Join Date
    Mar 2011
    Location
    Bellingham, WA
    Posts
    450
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    You are correct... and thanks for the link! I read through the page, and have tried the following:

    SELECT *
    FROM homepage_homeschool_schedule
    WHERE dbTime < (SELECT CURRENT_TIME - INTERVAL 3 DAY)

    I changed TIMESTAMP to read dbTime because I thought that maybe using TIMESTAMP as a variable name wouldn't be a good idea. Regardless, now I have no error, but whether by inequality is less than or greater than, I get no rows back.

    My goal is to get all records with dbTimes that are earlier than 3 days in the past. Any additional thoughts or hints would be appreciated.

    -Eric

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by kreut View Post
    Any additional thoughts or hints would be appreciated.
    did you try it the way i suggested?
    Code:
    WHERE dbtime < CURRENT_DATE - INTERVAL 3 DAY
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Evangelist
    Join Date
    Mar 2011
    Location
    Bellingham, WA
    Posts
    450
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    r937:

    Thank you for the solution and your patient response: due to a lag in receiving your messages, it only seemed as though I wasn't taking your advice right off the bat.

    Have a great day...

    -Eric


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
  •