Page 1 of 1
Day of Week problem Language
Posted: 2020-06-19 15:40
by 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
Re: Day of Week problem Language
Posted: 2020-06-20 07:20
by pbottcher
Can you post the formula
Re: Day of Week problem Language
Posted: 2020-06-20 07:53
by 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
Re: Day of Week problem Language
Posted: 2020-06-20 08:57
by pbottcher
Try
Code: Select all
SET @@lc_time_names = 'it_IT';SELECT DAYNAME(`Servizi`.`data_servizio`) FROM `Servizi`
WHERE `Servizi`.`id_servizio`='%ID%'
Re: Day of Week problem Language
Posted: 2020-06-27 16:46
by lzonca
hi, I replaced the query on the calculated field, but it doesn't work, remains empty
Re: Day of Week problem Language
Posted: 2020-06-27 19:12
by 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%'
Re: Day of Week problem Language
Posted: 2020-07-06 13:02
by lzonca
Thanks, I tried the new code, but it doesn't work. empty field
Re: Day of Week problem Language
Posted: 2020-07-06 19:34
by 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.
Re: Day of Week problem Language
Posted: 2020-07-06 21:15
by 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%'
Re: Day of Week problem Language
Posted: 2020-07-06 21:39
by 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
Re: Day of Week problem Language
Posted: 2020-07-08 19:46
by lzonca
jsetzer wrote: ↑2020-07-06 21:15
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?
Re: Day of Week problem Language
Posted: 2020-07-08 21:17
by 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 4602 times
Just replace the 7 entries
'Dom', 'Lun', 'Mar', 'Mer', 'Gio', 'Ven', 'Sab' by any different seven entries, wrapped in single-quotes, each
Re: Day of Week problem Language
Posted: 2020-07-10 19:12
by lzonca
I'm sorry, I didn't explain myself well.
I'll explain it with screens