Day of Week problem Language

[lzonca]

Hi everyone .
I applied this formula to receive the day of the week (calculated Fields) but it is returned to me in English. how do I get it in Italian ?? Thanks

[pbottcher]

Can you post the formula

[lzonca]

OPS!!
this is the code calculated Fields

Code: Select all

SELECT DAYNAME(`Servizi`.`data_servizio`) FROM `Servizi` 
WHERE `Servizi`.`id_servizio`='%ID%'

data_servizi is data from timepicker

[pbottcher]

Try

Code: Select all

SET @@lc_time_names = 'it_IT';SELECT DAYNAME(`Servizi`.`data_servizio`) FROM `Servizi` 
WHERE `Servizi`.`id_servizio`='%ID%'

[lzonca]

hi, I replaced the query on the calculated field, but it doesn't work, remains empty

[pbottcher]

Can you run

Code: Select all

SET lc_time_names = 'it_IT';SELECT DAYNAME(`Servizi`.`data_servizio`) FROM `Servizi` 
WHERE `Servizi`.`id_servizio`='%ID%'

[lzonca]

Thanks, I tried the new code, but it doesn't work. empty field

[pbottcher]

Hi,

after looking deeper into it, i'm afraid that this is not possible to do it via the calculated field.
All i could suggest is that you use replace(FIELD, SEARCH, TARGET) and use it multiple times for the whole week.

[jsetzer]

Code: Select all

SELECT ELT(dayofweek(`FIELDNAME`), 'Dom', 'Lun', 'Mar', 'Mer', 'Gio', 'Ven', 'Sab')
FROM `TABLENAME` 
WHERE `PKCOLUMN`='%ID%' 

Please note that first entry in list of days is Sunday, because (by default) week starts on Sundays in MySQL databases.

Just replace:

• FIELDNAME (your date field)
• TABLENAME your table name
• PKCOLUMN your primary key column name

In your specific case:

Code: Select all

SELECT ELT(dayofweek(`data_servizio`), 'Dom', 'Lun', 'Mar', 'Mer', 'Gio', 'Ven', 'Sab')
FROM `Servizi` 
WHERE `id_servizio`='%ID%' 

[jsetzer]

(2) different approach

Step 1:
Create a table (in your SQL tool or by code) and populate id's 1-7 with your localized weekday names, starting with 1=Sunday...7=Saturday.

Code: Select all

DROP TABLE IF EXISTS `i18n_weekdays`;
CREATE TABLE `i18n_weekdays` (
  `id` int(11) NOT NULL,
  `lang_code` varchar(5) NOT NULL,
  `weekdayname` varchar(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `i18n_weekdays` (`id`, `lang_code`, `weekdayname`) VALUES
(1,	'de',	'Sonntag'),
(2,	'de',	'Montag'),
(3,	'de',	'Dienstag'),
(4,	'de',	'Mittwoch'),
(5,	'de',	'Donnerstag'),
(6,	'de',	'Freitag'),
(7,	'de',	'Samstag');

Step 2:
Your calculated field SQL:

Code: Select all

SELECT `i18n_weekdays`.`weekdayname`
FROM `TABLENAME` INNER JOIN `i18n_weekdays` 
ON dayofweek(`TABLENAME`.`FIELDNAME`) = `i18n_weekdays`.`id` 
AND `i18n_weekdays`.`lang_code` = 'de'
WHERE `TABLENAME`.`PKFIELDNAME`='%ID%'

2nd approach is a bit more complicated but also more flexible for future purposes. If you only focus on one language, you do not even need the lang_code column but just id and name columns.

Regards,
Jan

[lzonca]

Code: Select all

SELECT ELT(dayofweek(`FIELDNAME`), 'Dom', 'Lun', 'Mar', 'Mer', 'Gio', 'Ven', 'Sab')
FROM `TABLENAME` 
WHERE `PKCOLUMN`='%ID%' 

Please note that first entry in list of days is Sunday, because (by default) week starts on Sundays in MySQL databases.

Just replace:

• FIELDNAME (your date field)
• TABLENAME your table name
• PKCOLUMN your primary key column name

In your specific case:

Code: Select all

SELECT ELT(dayofweek(`data_servizio`), 'Dom', 'Lun', 'Mar', 'Mer', 'Gio', 'Ven', 'Sab')
FROM `Servizi` 
WHERE `id_servizio`='%ID%' 

Great. Thanks, Solution 1 it works, but I only have one small problem. Eg (Mon = Monday) the problem is the accented letters, Monday = Monday? is it possible to solve this problem?

[jsetzer]

Sorry, I do not understand.

You can change the resulting daynames to whatever you like, for example to strings having any international characters

Code: Select all

SELECT ELT(dayofweek(`date`), 'äöü', 'ÄÖÜ', 'ß', 'áà', 'â', 'Ææ', '♠♣♥♦')
FROM `vouchers` 

chrome_Gu9NviR1Hr.png (10.58 KiB) Viewed 4589 times

Just replace the 7 entries 'Dom', 'Lun', 'Mar', 'Mer', 'Gio', 'Ven', 'Sab' by any different seven entries, wrapped in single-quotes, each

[lzonca]

I'm sorry, I didn't explain myself well.
I'll explain it with screens