CODEWITHSUNDEEP
oracle-databaseplsqloracle11goracle-sqldeveloper
denisb
denisb

Reputation: 787

Oracle NLS_DATE_LANGUAGE - Month Abbreviations

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"

Upvotes: 1

Views: 12377

Answers (3)

Boneist
Boneist

Reputation: 23578

Here's one way of doing it - note that you didn't mention September, which is commonly abbreviated to Sept.:

with months as (select add_months(trunc(sysdate, 'yyyy'), level -1) mon
                from   dual
                connect by level <= 12)
select mon,
       case when to_char(mon, 'mm') in ('05', '06', '07') then to_char(mon, 'fmMonth fmdd, yyyy', 'NLS_DATE_LANGUAGE = English')
            when to_char(mon, 'mm') = '09' then substr(to_char(mon, 'fmMonth', 'NLS_DATE_LANGUAGE = English'), 1, 4)||to_char(mon, '. dd, yyyy', 'NLS_DATE_LANGUAGE = English')
            else to_char(mon, 'Mon. dd, yyyy', 'NLS_DATE_LANGUAGE = English') 
       end abbrev_mon
from   months;

MON       ABBREV_MON                          
--------- ------------------------------------
01-JAN-16 Jan. 01, 2016                       
01-FEB-16 Feb. 01, 2016                       
01-MAR-16 Mar. 01, 2016                       
01-APR-16 Apr. 01, 2016                       
01-MAY-16 May 01, 2016                        
01-JUN-16 June 01, 2016                       
01-JUL-16 July 01, 2016                       
01-AUG-16 Aug. 01, 2016                       
01-SEP-16 Sept. 01, 2016                      
01-OCT-16 Oct. 01, 2016                       
01-NOV-16 Nov. 01, 2016                       
01-DEC-16 Dec. 01, 2016

Upvotes: 2

Alex Poole
Alex Poole

Reputation: 191275

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.

Upvotes: 4

MT0
MT0

Reputation: 167972

Oracle Setup

CREATE FUNCTION to_char_abbr_month(
  in_date DATE,
  in_nls  VARCHAR2
) RETURN VARCHAR2 DETERMINISTIC
IS
BEGIN
  RETURN REGEXP_REPLACE(
           TRIM( TO_CHAR( in_date, 'Month', in_nls ) ),
           '(\w{3})\w{2,}',
           '\1.'
         )
         || ' ' || TO_CHAR( in_date, 'dd, yyyy', in_nls );
END;
/

Query:

SELECT to_char_abbr_month(
         DATE '2016-01-01',
         'NLS_DATE_LANGUAGE = American'
       )
FROM   DUAL

Output

Jan. 01, 2016

Upvotes: 1

Related Questions