DateTime Error

[Dan_Mason]

Hello there!

 

I have written this MySQL function:

 

DROP FUNCTION IF EXISTS optimaldb.TBC;
CREATE FUNCTION optimaldb.`TBC`(SectionID int(10), StartTime datetime) RETURNS datetime
BEGIN
  DECLARE PrevTime datetime;
  SELECT EndTime FROM PassSection WHERE EndTime < StartTime ORDER BY SectionID LIMIT 1 INTO PrevTime;
  RETURN StartTime - PrevTime;
END;

 

Into which I pass 2 variables i.e:

SELECT TBC(3451,'2010-04-01 06:03:12')

 

This is the error that keeps getting thrown up: MySQL Database Error: Incorrect datetime value: '9188914574.000000' for column 'TBC' at row 1

 

I'm not entirely sure what I have done wrong  as I have looked up this: http://dev.mysql.com/doc/refman/4.1/en/datetime.html and as far as I can see I am structuring the datetime correctly!

 

Any help would be really handy!

 

Thanks, Dan

 

[Muddy_Funster]

I think you want to use a different function on the date, using a minus converts it on the fly to a unix time value.  Try using DATEDIFF or DATE_SUB and see what you get (or CAST the result back into date format after you have applied your logic to it).