Mysql – Creating trigger to manipulate language

MySQL

This is the table that I have. Effective date is a user input, then I need to use a trigger that produces Print depending on the language the user inputs (English or French only).

For English, I used DATE_FORMAT to achieve the output as shown, but I am completely lost on what I should do if the user chooses French. The desired output would be 23 Mai 2016.

I was thinking of storing all the months in a separate table and then calling that table depending on the month component of the date provided but I'm still not sure if that will work. Any help is appreciated.

╔══════════╤════════════════╤═════════════╗
║ Language │ Effective Date │ Print       ║
╠══════════╪════════════════╪═════════════╣
║ English  │ 23/05/2016     │ 23 May 2016 ║
╟──────────┼────────────────┼─────────────╢
║ French   │ 23/05/2016     │             ║
╚══════════╧════════════════╧═════════════╝

Edit:

Trigger Code:

DELIMITER |

CREATE TRIGGER printdate
BEFORE INSERT ON `h&a` FOR EACH ROW
BEGIN 
  IF NEW.language = "English" 
  THEN
  SET NEW.print = DATE_FORMAT(NEW.`effective date`, '%d %M %Y')
  END IF;
  END|

 DELIMITER ;

Best Answer

Here is a way to get the name of the month in French, then you can use CONCAT to construct the date however you want:

SET @month=month("23/05/2016");
SELECT CASE @month 
    WHEN 1 THEN "janvier"
    WHEN 2 THEN "février"
    WHEN 3 THEN "mars"
    WHEN 4 THEN "avril"
    WHEN 5 THEN "mai"
    WHEN 6 THEN "juin"
    WHEN 7 THEN "juillet"
    WHEN 8 THEN "août"
    WHEN 9 THEN "septembre"
    WHEN 10 THEN "octobre"
    WHEN 11 THEN "novembre"
    WHEN 12 THEN "décembre"
END INTO @nom_de_mois;
SELECT @nom_de_mois;

Working example:

mysql> SET @month=month("2016-05-23");
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT CASE @month 
    ->     WHEN 1 THEN "janvier"
    ->     WHEN 2 THEN "février"
    ->     WHEN 3 THEN "mars"
    ->     WHEN 4 THEN "avril"
    ->     WHEN 5 THEN "mai"
    ->     WHEN 6 THEN "juin"
    ->     WHEN 7 THEN "juillet"
    ->     WHEN 8 THEN "août"
    ->     WHEN 9 THEN "septembre"
    ->     WHEN 10 THEN "octobre"
    ->     WHEN 11 THEN "novembre"
    ->     WHEN 12 THEN "décembre"
    -> END INTO @nom_de_mois;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @nom_de_mois;
+--------------+
| @nom_de_mois |
+--------------+
| mai          |
+--------------+
1 row in set (0.00 sec)

mysql> SELECT CONCAT(date_format("2016-05-23", "%d"), " ", @nom_de_mois, " ", date_format("2016-05-23", "%Y")) AS date_in_French;
+----------------+
| date_in_French |
+----------------+
| 23 mai 2016    |
+----------------+
1 row in set (0.00 sec)
Related Question