CODEWITHSUNDEEP
mysqlfunction
Dathon Weber
Dathon Weber

Reputation: 71

How can I fix a MySQL Error, Not allowed to return a result set from a function

CREATE FUNCTION `ConvertDate`(StringDate varchar(15))
RETURNS datetime
BEGIN
    declare CDATE varchar(10);
    SET CDATE = StringDate;
    select str_to_date(CDATE,'%Y%m%d %h%i');
    RETURN CDATE;
END

I get an error:

Error Code: 1415 Not allowed to return a result set from a function

How can I fix this Error?

Upvotes: 0

Views: 57

Answers (2)

Nick
Nick

Reputation: 147146

Your problem is that your SELECT statement has no INTO clause, so it attempts to return the result set from the function. You could alter that to:

SELECT STR_TO_DATE(CDATE,'%Y%m%d %h%i') INTO CDATE;

But you might as well do all the computation in the RETURN statement:

CREATE FUNCTION `ConvertDate2`(StringDate varchar(15))
RETURNS datetime
RETURN STR_TO_DATE(StringDate,'%Y%m%d %h%i');

Upvotes: 2

P.Salmon
P.Salmon

Reputation: 17615

You should 

Select INTO cdate...

or don't bother with the select and 

set cdate = str_to_date..

BUT Unless this is a very simplified version of your actual function I don's see the point of the function.

Upvotes: 0

Related Questions