Learning foreign languages with Oracle SQL
Posted by Matthias Rogel on 23. March 2012
with y as
(
select
add_months(date'2012-01-01', level-1) monn,
to_char(add_months(date'2012-01-01', level-1), 'MONTH') mon
from dual
connect by level<=12
)
select
value as language,
y.mon,
to_char(y.monn, 'MONTH', q'|nls_date_language='|' || value || q'|'|') month,
to_char(y.monn, 'MON', q'|nls_date_language='|' || value || q'|'|') month_s
from v$nls_valid_values n, y
where n.parameter='LANGUAGE'
order by language, y.monn
TRADITIONAL CHINESE looks easy
Use SQL to Create a Calendar in Mutliple Languages » Eddie Awad's Blog said
[…] his post titled learning foreign languages with Oracle SQL, Matthias Rogel shares the following query (I slightly modified and formatted it for […]
Use SQL to Create a Calendar in Mutliple Languages « oracle fusion identity said
[…] his post titled learning foreign languages with Oracle SQL, Matthias Rogel shares the following query (I slightly modified and formatted it for […]
Use SQL to Create a Calendar in Mutliple Languages | Oracle Administrators Blog - by Aman Sood said
[…] SQL to Create a Calendar in Mutliple Languages In his post titled learning foreign languages with Oracle SQL, Matthias Rogel shares the following query (I slightly modified and formatted it for […]
Chris Saxon said
Neat, I like it!
We could go for days of the week as well:
WITH y AS
(SELECT add_months(DATE’2012-01-01′, level -1) dayy,
TO_CHAR(add_months(DATE’2012-01-01′, level-1), ‘DAY’) dy
FROM dual
CONNECT BY level<=7
)
SELECT value AS language,
y.dy,
TO_CHAR(y.dayy, 'DAY', q'|nls_date_language='|'
|| value
|| q'|'|') DAY, to_char(y.dayy, 'DAY', q'|nls_date_language='|'
|| value
|| q'|'|') DAY_s
from v$nls_valid_values n, y
where n.parameter='LANGUAGE'
order by language, y.dayy
Matthias Rogel said
Chris,
thanks for that addendum !
Matthias
Julius Zaldokas (@JuliusZaldokas) said
Chris, the 7 days of the week subquery should be with “+” instead of “add_months”. As in:
WITH y AS
(SELECT DATE’2012-01-01′ + (level -1) dayy,
TO_CHAR(DATE’2012-01-01′ + (level-1), ‘DAY’) dy
FROM dual
CONNECT BY level<=7
)
Otherwise, not all possible days are translated with some repetitions.
Fun stuff!! 🙂
State of Data #96 « Dr Data's Blog said
[…] #DBMS – Single SQL to create calendar in multiple languages […]
Learning foreign languages with Oracle PL/SQL » SQLfail said
[…] on the fantastic calendar example from Sokrates/Matthias […]