SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Wizard westmich's Avatar
    Join Date
    Mar 2000
    Location
    Muskegon, MI
    Posts
    2,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Using DateDiff Function in MySQL

    I think the following error relates to the DateDiff function in my syntax, but I am not sure.

    ERROR [42000] [MySQL][ODBC 3.51 Driver][mysqld-4.0.18-nt]You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '('StartDateTime','EndDateTime')/24/60) AS TotalMinutes, StartDa

    SQL
    Code:
    Select Sum(DateDiff('n',StartDateTime,EndDateTime)) AS TotalMinutes, StartDate 
    From Scheduled_WorkOrders
    Where EmployeeID = 1
    Group By StartDate
    I read the documentation on MySQL.com and made the following changes, but I am still getting the same error.

    SQL
    Code:
    Select Sum(DateDiff('StartDateTime','EndDateTime')/24/60) AS TotalMinutes, StartDate 
    From Scheduled_WorkOrders
    Where EmployeeID = 1
    Group By StartDate
    Westmich
    Smart Web Solutions for Smart Clients
    http://www.mindscapecreative.com

  2. #2
    SitePoint Wizard westmich's Avatar
    Join Date
    Mar 2000
    Location
    Muskegon, MI
    Posts
    2,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well, I think I found the answer to my question -
    DATEDIFF() and TIMEDIFF() were added in MySQL 4.1.1
    Does anyone know a work-a-round to this?
    Westmich
    Smart Web Solutions for Smart Clients
    http://www.mindscapecreative.com

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    soiteny!! nyuk nyuk nyuk
    Code:
    select sum(
             unix_timestamp(EndDateTime)
           - unix_timestamp(StartDateTime)
              ) / 60 as TotalMinutes
         , StartDate 
      from Scheduled_WorkOrders
     where EmployeeID = 1
    group 
        by StartDate
    unix_timestamp() converts a datetime to an integer number of seconds

    the actual value does not matter if you are subtracting two of these
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Zealot avolia's Avatar
    Join Date
    Jun 2001
    Location
    USA
    Posts
    155
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I am running into the same issue here.
    I am trying to pull all record less the 7 days old. I tried the datediff function but I got the same error above. So I tried doing the following:
    SELECT id, title, created FROM `table`
    WHERE ( CURDATE( ) - created ) <8
    Group by created


    This is giving me everything in the table which is not what I am looking for.
    Any idea on how to do this? The created field type is "datetime"

    Thanks.

  5. #5
    SitePoint Wizard westmich's Avatar
    Join Date
    Mar 2000
    Location
    Muskegon, MI
    Posts
    2,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Can you add the date from a server-side language?
    Code:
    ASP.NET (VB)
    Dim myDate as Date
    myDate = Now.AddDays(-7)
    Dim SQL as String = "Select ... Where Current > " & myDate & "
    That's a rough example.
    Westmich
    Smart Web Solutions for Smart Clients
    http://www.mindscapecreative.com

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by avolia
    SELECT id, title, created FROM `table`
    WHERE ( CURDATE( ) - created ) <8
    Group by created
    this one's a bit wacky because of the GROUP BY, which i will ignore

    remember, you don't want to apply the function to the datetime column because that will usually mean the index cannot be used
    Code:
    select id
         , title
         , created 
      from `table` 
     where created > date_add(current_date, interval -8 day)
    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
  •