Using DateDiff Function in MySQL

[westmich]

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

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

Select Sum(DateDiff('StartDateTime','EndDateTime')/24/60) AS TotalMinutes, StartDate 
From Scheduled_WorkOrders
Where EmployeeID = 1
Group By StartDate

[westmich]

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?

[r937]

soiteny!! nyuk nyuk nyuk

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

[avolia]

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.

[westmich]

Can you add the date from a server-side language?

ASP.NET (VB)
Dim myDate as Date
myDate = Now.AddDays(-7)
Dim SQL as String = "Select ... Where Current > " & myDate & "

That's a rough example.

[r937]

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

select id
     , title
     , created 
  from `table` 
 where created > date_add(current_date, interval -8 day)