enter image description hereI have created a datetoword function in Mysql. Checked the function working fine. Now using this function need to convert the date from field1 (date-datetype) to field2 (Varchar-datatype) within the table using trigger on before insert.

Tried with some trigger code

Set New.dobw = CALL date_to_words(New.dob);

Help needed in this regard.

Show function status; (output is shown in screenshot)

SELECT date_to_words(NOW()) SELECT date_to_words(NOW())

  • Please run SHOW CREATE FUNCTION date_to_words\G and post the output. Feb 6, 2015 at 19:04
  • As a matter of interest, why did you do this? DATEs should be stored as DATEs, VARCHARs as VARCHARS and so on.
    – Vérace
    Feb 6, 2015 at 19:14
  • i need for my application the date field in date and in word format for printing in reports
    – Acube
    Feb 7, 2015 at 5:00
  • cant post the full function, too long to post the output of the SHOW CREATE FUNCTION date_to_words\G
    – Acube
    Feb 7, 2015 at 6:35
  • Here is a question: What exactly would the output of the date_to_words function produce ? For example, what would be the output of SELECT date_to_words(NOW()); ? Feb 7, 2015 at 20:53

2 Answers 2


Based on your comment, the trigger has multiple lines.

You need a BEGIN...END block as follows:

USE mcet
DROP TRIGGER IF EXISTS tg_student_details_insert;
CREATE TRIGGER tg_student_details_insert BEFORE INSERT
ON student_details FOR EACH ROW
    SET NEW.created_date = NOW();
    SET NEW.dobw = date_to_words(NEW.dob);

You answered this question before (How to autoincrement Prod_id based on Product_Name using trigger in mysql). In that post, you used a BEGIN...END block for four(4) lines of code. You needed one for this post as well.


If it is a Stored Function, remove the keyword "CALL".

If that does not suffice, please show us

  • after removin CALL form the trigger also its not working.Set New.dobw = date_to_words(New.dob);
    – Acube
    Feb 7, 2015 at 5:06
  • The whole TRIGGER and the whole table, please. Plus any error message associated with "not working".
    – Rick James
    Feb 7, 2015 at 5:33
  • CREATE TRIGGER tg_student_details_insert BEFORE INSERT ON student_details FOR EACH ROW SET NEW.created_date = NOW(); Set New.dobw = CALL date_to_words(New.dob); I am getting a Syntax error.
    – Acube
    Feb 7, 2015 at 6:41
  • You added back the "CALL"? I assume the syntax error pointed either to 'CALL...' or 'date_to_words...', correct?
    – Rick James
    Feb 7, 2015 at 6:44
  • 1
    Please show us the entire error message. Syntax error always points to where the error is, although sometimes it says "near ''", which implies it fell off the end expecting something else.
    – Rick James
    Feb 7, 2015 at 17:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.