Oracle NLS_DATE_LANGUAGE - Month Abbreviations

Question

I'm having issues with proper month abbreviation values based on what I can find online (for example) whereas I've tried using both "English" and "American" in my NLS_DATE_LANGUAGE parameter, but the underlying values aren't displaying what I'd like to based on the common standard found through various other sites.

As mentioned, I've tried both:

SELECT TO_CHAR(pDate, 'Mon dd, yyyy', 'NLS_DATE_LANGUAGE = American') FROM DUAL;
SELECT TO_CHAR(pDate, 'Mon dd, yyyy', 'NLS_DATE_LANGUAGE = English') FROM DUAL;

My months are coming out as followed, so most months should have the trailing "." value except for May, June, and July, and "Jun", should be "June":

Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec

I can easily add a format exception based on the month (e.g. "Mon." instead of "Mon", except for months 5-6-7), but just figured I'd ask the question if someone knows of any other language or territory values that could resolve this instead of creating some exception logic by month. I also produce the output with "French" and the output works perfectly with the abbreviations including the "." despite just passing "Mon dd, YYYY"

Alex Poole Alex Poole · Accepted Answer · 2016-05-06T13:41:55

There is no Oracle NLS_DATE_LANGUAGE value that produces the output you expect (I just checked them all!). You need special handling for certain values, which is mostly based on length, but the example you linked to and other style guides (e.g. AP and Princeton, and this) suggest you should have 'Sept.' for September, rather than Sep..

The simplest thing is to hard-code them:

select case extract(month from add_months(trunc(sysdate, 'YYYY'), level - 1))
  when 1 then 'Jan.' when 2 then 'Feb.' when 3 then 'Mar.' when 4 then 'Apr.'
  when 5 then 'May' when 6 then 'June' when 7 then 'July' when 8 then 'Aug.'
  when 9 then 'Sept.' when 10 then 'Oct.' when 11 then 'Nov.' else 'Dec.' end
from dual
connect by level <= 12;

CASEE
-----
Jan. 
Feb. 
Mar. 
Apr. 
May  
June 
July 
Aug. 
Sept.
Oct. 
Nov. 
Dec.

You can use a function to use your custom conversion:

create or replace function my_to_char(p_date date, p_fmt varchar2) return varchar2 is
  l_str varchar2(30);
  l_month varchar2(5);
begin
  l_month := case extract(month from p_date)
    when 1 then 'Jan.' when 2 then 'Feb.' when 3 then 'Mar.' when 4 then 'Apr.'
    when 5 then 'May' when 6 then 'June' when 7 then 'July' when 8 then 'Aug.'
    when 9 then 'Sept.' when 10 then 'Oct.' when 11 then 'Nov.' else 'Dec.' end;

  return to_char(p_date, replace(p_fmt, 'Mon', '"' || l_month || '"'));
end;
/

select my_to_char(date '2016-04-15' , 'DD Mon YYYY') as result from dual;

RESULT             
--------------------
15 Apr. 2016

I've kept the function simple (ish); you need to be more clever about replacing 'Mon', (a) so you don't catch 'Month', and (b) to allow for different case ('mon', 'Mon', 'MON'). You may not actually need that amount of flexibility though, and if you do you can extend this kind of approach.

Oracle NLS_DATE_LANGUAGE - Month Abbreviations

3 Answers