Hi,
This is not really a question, but a follow-up to this thread: determine if date is a weekend
where the question arose "Are there any languages where the Oracle weekday abbreviations are different lengths for different days?" For example, in English, the weekday names range from 6 to 9 characters, but the abbreviations that you get from TO_CHAR (dt, 'fmDY') are always 3 characters long.
It seems there are many such languages. Of all the languages that you can use in 'NLS_DATE_LANGUAGE' in Oracle 12.1, 28 have varying-length abbreviations.
Here's how I learned that:
WITH all_weekdays AS
(
SELECT SYSDATE + LEVEL AS dt
FROM dual
CONNECT BY level <= 7
)
, language_info AS
(
SELECT v.value AS language
, TO_CHAR ( d.dt
, 'fmDY'
, 'NLS_DATE_LANGUAGE=''' || v.value || ''''
) AS dy
, TO_CHAR ( d.dt
, 'fmDAY'
, 'NLS_DATE_LANGUAGE=''' || v.value || ''''
) AS day
FROM all_weekdays d
CROSS JOIN v$nls_valid_values v
WHERE v.parameter = 'LANGUAGE'
AND v.isdeprecated = 'FALSE'
)
SELECT language
, MIN (LENGTH (dy)) AS shortest
, MAX (LENGTH (dy)) AS longest
, MAX (LENGTH (dy))
- MIN (LENGTH (dy)) AS diff
FROM language_info
GROUP BY language
HAVING MAX (LENGTH (dy)) > MIN (LENGTH (dy))
AND COUNT ( CASE
WHEN day <> dy
THEN 'Different'
END
) > 0
ORDER BY language
;
Output:
LANGUAGE SHORTEST LONGEST DIFF
---------------------- ---------- ---------- ----------
AMHARIC 2 3 1
ASSAMESE 3 8 5
AZERBAIJANI 1 2 1
BANGLA 3 8 5
CYRILLIC UZBEK 1 3 2
GREEK 2 3 1
GUJARATI 3 5 2
HINDI 3 5 2
HUNGARIAN 2 3 1
IRISH 4 5 1
KANNADA 3 5 2
KHMER 1 3 2
LAO 2 3 1
LATIN UZBEK 3 5 2
MALAYALAM 3 7 4
MALTESE 3 4 1
MARATHI 3 5 2
NEPALI 3 5 2
ORIYA 3 5 2
POLISH 1 2 1
PUNJABI 2 6 4
ROMANIAN 1 2 1
SINHALA 2 5 3
SWAHILI 2 3 1
TAMIL 3 7 4
TELUGU 3 5 2
THAI 2 3 1
VIETNAMESE 6 8 2
28 rows selected.
Oracle 12.1 supports 78 different values for NLS_DATE_LANGUAGE. (Some of these aren't much, if at all, different: e.g., I've never noticed any difference between AMERICAN and ENGLISH regarding weekday or month names.) Of those 78 languages 8 don't have abbreviations for weekdays in Oracle, that is TO_CHAR (dt, 'fmDAY') is always the same as TO_CHAR (dt, 'fmDY') , and of the remaining 70, the 28 above have varying-length abbreviations.
A couple of these languages (Assamese and Bangla) have more variation in their abbreviation lengths (3 to 8) than English does in the full names,
Thanks to Sven W. for his help with this question.