0

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 ;
  • On the command line, locale -a lists the installed locales. On Debian dpkg-reconfigure locales lets you add more. Then you can use set_config('lc_time', 'fr_FR', True); in your trigger before printing. To get a localised month use select to_char(NEW.date::date,'TMmon');. I'm afraid I haven't put together a demo for you but hopefully that should be enough to get you where you need to be! Which database are you using? This should work for postgres. – Max Murphy May 30 '16 at 16:18
  • @MaxMurphy Thank you for your reply. I'm afraid your answer is beyond the scope of my understanding. I've never used the command line but I will look it up and try to figure out something. I'm using MySQL. – ybce May 30 '16 at 16:40
0

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)
  • Thank you, I ended up creating a table with the same structure as your SELECT CASE statement and then I used EXTRACT to extract the month number from the short date and then used a SELECT statement to set my french_month variable and finally CONCAT to put everything together. I will mark your answer as correct as it's a nicer solution. – ybce May 30 '16 at 19:46

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.