1

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. – RolandoMySQLDBA Feb 6 '15 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 '15 at 19:14
  • i need for my application the date field in date and in word format for printing in reports – Acube Feb 7 '15 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 '15 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()); ? – RolandoMySQLDBA Feb 7 '15 at 20:53
1

Based on your comment, the trigger has multiple lines.

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

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

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.

0

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

If that does not suffice, please show us

SHOW CREATE TRIGGER ...
SHOW CREATE TABLE ...
  • after removin CALL form the trigger also its not working.Set New.dobw = date_to_words(New.dob); – Acube Feb 7 '15 at 5:06
  • The whole TRIGGER and the whole table, please. Plus any error message associated with "not working". – Rick James Feb 7 '15 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 '15 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 '15 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 '15 at 17:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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