I am just starting out with MySQL and am having trouble with creating
a simple stored function. I want to pass a date value and have the
function to return the number of months between the specified date and
the current date. Using the following SQL I get a syntax error #1064:

CREATE FUNCTION Months_Ahead(Input_Date Date)
RETURNS Integer
DETERMINISTIC
begin
RETURN period_diff(concat(Year(Input_Date), lpad(Month(Input_Date),
2,0)), concat(Year(curdate()), lpad(Month(curdate()),2,0)));
end

But when I run the following SQL (which I thought should be what the
stored function would be trying to run, given the same input) it works
as expected:

SELECT period_diff(concat(Year('2011-09-01'), lpad(Month('2011-09-01'),
2,0)), concat(Year(curdate()), lpad(Month(curdate()),2,0)))

I must be missing something obvious, but as a newbie I can't spot what
it is. Hopefully more experienced eyes can see it?

Thanks in advance


Martin

On 12/13/2010 6:26 PM, MartinH wrote:
How are you creating the function? From the MySQL CLI, for instance?
If so, you need to set a delimiter because the ';' is used inside the
function. For instance, the following works from the MySQL CLI:

Delimiter //
CREATE FUNCTION Months_Ahead(Input_Date Date)
RETURNS Integer
DETERMINISTIC
begin
RETURN period_diff(concat(Year(Input_Date), lpad(Month(Input_Date),
2,0)), concat(Year(curdate()), lpad(Month(curdate()),2,0)));
end;
//
delimiter ;



--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

On Dec 14, 12:10*pm, Jerry Stuckle <jstuck... (AT) attglobal (DOT) net> wrote:
Thanks Jerry, I was using phpMyAdmin and changing the delimiter solved
the problem.

El 14/12/2010 0:26, MartinH escribió/wrote:
BTW, the value of something like period_diff('2010-12-31') changes every
day so don't think your function is deterministic:

«A routine is considered “deterministic” if it always produces the same
result for the same input parameters, and “not deterministic” otherwise.
If neither DETERMINISTIC nor NOT DETERMINISTIC is given in the routine
definition, the default is NOT DETERMINISTIC. To declare that a function
is deterministic, you must specify DETERMINISTIC explicitly.
[...]
A routine that contains the NOW() function (or its synonyms) or RAND()
is nondeterministic, but it might still be replication-safe.»

<http://dev.mysql.com/doc/refman/5.1/en/create-procedure.html>



--
-- http://alvaro.es - Álvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programación web: http://borrame.com
-- Mi web de humor satinado: http://www.demogracia.com
--