SitePoint Sponsor

User Tag List

Results 1 to 6 of 6

Thread: datediff syntax

  1. #1
    SitePoint Member
    Join Date
    Oct 2009
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    datediff syntax

    hi can anyone see what is wrong with this syntax? it is driving me mad!! thanks in advance

    Paul

    SELECT MAX(caldate) AS Date

    FROM (

    SELECT TOP 5 caldate

    FROM Calendar

    WHERE caldate > date add(day, DATEDIFF (day, 0, curdate()), 0)

    and WORKINGDAY = 'Y'

    ORDER BY caldate

    ) c

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    it's simple, really

    mysql doesn't support TOP

    and the DATEADD and DATEDIFF functions are different, too

    are you sure you're using mysql?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Oct 2009
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hah thanks yeah i have just realised i should be using limit...
    very new to mysql i am used to mssql...
    do you think you could convert the above query to work with mysql?

  4. #4
    SitePoint Member
    Join Date
    Oct 2009
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    think i have figured it out...

    select caldate from calendar where caldate =
    (select max(caldate) from (select caldate from calendar where caldate >now() and workingday = 'Y' limit 5)c)

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    i could convert it if i understood it

    in the subquery, the WHERE clause appears to be filtering on caldate so that only values greater than today are retrieved

    then you sort these into ascending sequence and take the first 5

    and then in the outer query, you're taking the 5th one

    in mysql, you can do it like this --
    Code:
    SELECT caldate AS Date
      FROM calendar
     WHERE caldate > CURRENT_DATE
       AND workingday = 'Y'
    ORDER 
        BY caldate LIMIT 4,1
    LIMIT 4,1 skips over 4 rows and returns the next 1
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    is caldate a DATE or a DATETIME column?
    r937.com | rudy.ca | 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
  •