HI,
I have oracle 12.1.0.2 database enterprise running on a linux server. I have two tables , one stores the month names and another stores the year numbers. Here is the data for the first table
select FISCAL_SYSTEM_PERIOD_CAL_YR_EQ, CALENDRICAL_EQUIVALENT
from fiscal_system_periods order by 2
| FISCAL_SYSTEM_PERIOD_CAL_YR_EQ | CALENDRICAL_EQUIVALENT |
--------------------------------- -------------------------
| January | 1 | |
| February | 2 | |
| March | 3 | |
| April | 4 | |
| May | 5 | |
| June | 6 | |
| July | 7 | |
| August | 8 | |
| September | 9 | |
| October | 10 | |
| November | 11 | |
| December | 12 | |
| 13 Series | (null) |
when I run the following query i am getting the invalid month error
select FISCAL_SYSTEM_PERIOD_CAL_YR_EQ, calendrical_equivalent
from fiscal_system_periods
where calendrical_equivalent > 0 and
to_date(FISCAL_SYSTEM_PERIOD_CAL_YR_EQ || ', ' || 2014,'Month, YYYY') >= to_date('November, 2014','Month, YYYY')
[Error] Script lines: 1-5 --------------------------
ORA-01843: not a valid month
and the data types are
| CALENDRICAL_EQUIVALENT | NUMBER |
FISCAL_SYSTEM_PERIOD_CAL_YR_EQ VARCHAR2
but if i run the following query, i am not getting any error
with
periods as (Select 'January' period, 1 cal From Dual
union all Select 'February' period , 2 cal From Dual
union all Select 'March' period , 3 cal From Dual
union all Select 'April' period , 4 cal From Dual
union all Select 'May' period, 5 cal From Dual
union all Select 'June' period, 6 cal From Dual
union all Select 'July' period, 7 cal From Dual
union all Select 'August' period, 8 cal From Dual
union all Select 'September' period, 9 cal From Dual
union all Select 'October' period, 10 cal From Dual
union all Select 'November' period, 11 cal From Dual
union all Select 'December' period, 12 cal From Dual
union all Select '13 Series' period, null cal From Dual )
select to_date(periods.period ||', '|| 2014,'Month, YYYY') period from periods where periods.cal > 0 and to_date(periods.period ||', '|| 2014,'Month, YYYY') = to_date('November, 2014','Month, YYYY')
i get the result
---------------------
11/1/2014 12:00:00 AM
I upgraded the DB today to 12.1.0.2 from 11.2.0. yesterday . Any one knows why this started happening ?
Thanks