Converting a Hebrew month name to an English month name in Oracle

Question

I have to take user input in Hebrew (a month name) and convert it to an English month name. Is there any way to convert this (maybe using to_date and to_char) without a lookup table?

Update - following the suggestion for Norwegian I made this test, showing that the short Hebrew month names are longer than three characters! (I can only handle three character strings in this function)

with d as
(
select to_date('01' || lpad(rownum,2,'0') || '2011','DDMMYYYY') d from
(
select 1 from dual connect by level <=12
)
)
select to_char(d.d,'MON','NLS_DATE_LANGUAGE=HEBREW') heb_mon,
to_char(d.d,'MONTH','NLS_DATE_LANGUAGE=AMERICAN') us_mon
from d;

Which produced this data

ינואר   JAN
פברואר  FEB
מרץ     MAR
אפריל   APR
מאי     MAY
יוני    JUN
יולי    JUL
אוגוסט  AUG
ספטמבר  SEP
אוקטובר OCT
נובמבר  NOV
דצמבר   DEC

Answer 1

Here's the first thing that came to my mind. I don't know enough hebrew to test with hebrew values, but this seems to work with norwegian:

with test_norwegian as (
  select 'januar' month
  from dual
)
select 
to_char(
  to_date('1 '||test_norwegian.month||' 2011', 'dd month yyyy', 'NLS_DATE_LANGUAGE=NORWEGIAN')
  , 'month', 'NLS_DATE_LANGUAGE=NORWEGIAN') norwegian_month
,to_char(
  to_date('1 '||test_norwegian.month||' 2011', 'dd month yyyy', 'NLS_DATE_LANGUAGE=NORWEGIAN')
  , 'month', 'NLS_DATE_LANGUAGE=AMERICAN') american_month
from test_norwegian

Answer 2

Based on a quick scan of the Wikipedia Article: Hebrew calendar it seems reasonable to say that the Hebrew calendar does not operate the same way as the Gregorian calendar. Examples include:

• There is no leap month in the Gregorian calendar for the year 5771.
• Gregorian months are fixed length. Since they are based on lunar cycles, not all Hebrew months are fixed length.

If you want to convert between Hebrew and Gregorian month names, You will first need to convert from a Hebrew date to a Gregorian date then determine the Gregorian month.

A google search for "convert from hebrew date to gregorian date" produces what appears to be a large number of tools for converting between these calendars.

Here is an sourceforge project that may apply hebcal