1

I am getting the following error:

1064 - SQL syntax; MySQL server RETURNS timestamp deterministic BEGIN DECLARE dt_act timestamp; sele' at line 1

This is my code: 

DROP FUNCTION IF EXISTS NewProc;
DELIMITER //
CREATE  FUNCTION NewProc(f_test varchar)
 RETURNS timestamp deterministic
BEGIN


DECLARE dt_act timestamp;
    select cast((case
when str_to_date(activation_date,'%d-%M-%Y') is not null then date_format(str_to_date(activation_date,'%d-%M-%Y'),'%Y-%m-%d') 
when str_to_date(activation_date,'%d-%m-%Y') is not null then date_format(str_to_date(activation_date,'%d-%m-%Y'),'%Y-%m-%d')
ELSE
date_format(str_to_date(activation_date,'%Y-%M-%d'),'%Y-%m-%d')
end) as timestamp ) into dt_act from abc where phone_no =f_test;

    RETURN dt_act;
END //

DELIMITER ;

ERROR:

1064 - 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 ') RETURNS timestamp deterministic BEGIN DECLARE dt_act timestamp; sele' at line 1

Simo
  • 955
  • 8
  • 18
Sayan Roychowdhury
  • 11
  • 3
  • Where is the question? https://stackoverflow.com/help/how-to-ask – Raymond Nijland Aug 30 '18 at 11:31
  • "**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" source https://dev.mysql.com/doc/refman/8.0/en/create-procedure.html. **With other words you are using `deterministic` wrong so you should remove it or change it into non deterministic** – Raymond Nijland Aug 30 '18 at 11:36
  • 1064 - 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 ') RETURNS timestamp deterministic BEGIN DECLARE dt_act timestamp; sele' at line 1 This is the error – Sayan Roychowdhury Aug 30 '18 at 11:48
  • You have not specified a size for your `VARCHAR` input, try changing it to `VARCHAR(50)` or something appropriate – Nick Sep 22 '18 at 02:17

0 Answers0