Which setting controls the language of 'TZD' or 'TZR' in the TO_CHAR(timestamp with time zone) function?

e.g. select to_char(systimestamp at TIME zone 'AMERICA/EDMONTON', 'TZD') from dual;

returns the English value for TZD "MDT" (Mountain Daylight Time), which is expected as the default value.

However, I can't find which parameter or setting to use for it to return the French value of TZD, i.e. "HNR"

  • I've tried adding ", 'nls_date_language=FRENCH'" to the TO_CHAR example above, as well as altering the session parameters NLS_LANG and NLS_DATE_LANGUAGE, but to no avail.

1 Answer 1


You can get the TZD abbreviations with this query:

SELECT tzabbrev, tzname, TZ_OFFSET(tzname) 
WHERE tzname = 'America/Edmonton'

----------  ------------------  ------------------
LMT         America/Edmonton    -06:00
MST         America/Edmonton    -06:00
MDT         America/Edmonton    -06:00
MWT         America/Edmonton    -06:00
MPT         America/Edmonton    -06:00

I think they are fix and you cannot change it. Note, for time zone abbreviations there is no common or official standard, for every time zone you will find dozens of abbreviations, see https://www.timeanddate.com/time/zones/na.

If you need a language specific output you could use CASE or DECODE

DECODE(to_char(systimestamp at TIME zone 'AMERICA/EDMONTON', 'TZD'), 'MDT','HNR' ,'HAR')

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.