ORA-01843: not a valid month error

building semantic layer
1

Hi all,

I have a number (2 digit) in an oracle database that represents a month and i need to convert it to Jan, Feb, Mar, etc… i used the following in the designer:

to_date(to_char(DAY.MONTH_ID),‘MON’)

but i’m getting the following error when trying to view the list of values:
ORA-01843: not a valid month :-1843

Can anyone help please?

Shamnaz

shamnaz (BOB member since 2004-04-21)

2

Try something along the lines of:

SELECT
   to_char ( to_date (to_Char(12) || '/1/1901', 'mm/dd/yyyy'), 'Mon')
FROM DUAL;

Keep in mind that the above code returns data type Character and as such the month names will sort alphabetically (for example: April, December, February, …).
You could leave it as data type Date and apply an object formatting in Designer instead to just show the month part.

Andreas :de: (BOB member since 2002-06-20)

3

If its a 2 digit year then shouldn’t you use

to_date(to_char(DAY.MONTH_ID),'MM')

ken.gaul :uk: (BOB member since 2002-06-18)

4

Hi

The problem is i need this Jan, Feb… Dec for a list of values… i’ve tried your suggestions and it is working but the resulting list of values in sorted in alphabetical order as you told me. Can you explain in more details how i can sort it from Jan to Dec please

Thanks

Shamnaz

shamnaz (BOB member since 2004-04-21)

5

Try search on BOB, suggested keywords: month sort LOV, author: Andreas

Andreas :de: (BOB member since 2002-06-20)

6

I’ve not tried this but can’t you put a custom sort on it?
Or can you put the numerical value in the LOV aswell sort on that but have them select the Char version?

ken.gaul :uk: (BOB member since 2002-06-18)

7

Look at this post on how to sort Month/Weekday names correctly.

Andreas :de: (BOB member since 2002-06-20)

8

Hi,

I did check the thread you proposed and i discovered a mojor difference between the case you were discussing and mine. In the other case, the month_id and month_name were both coming from database columns. This is not the case for me. I have the month_id but i am using the following code in a new object to get the month name:

to_char(to_date(@Aggregate_Aware( MONTH.MONTH_ID, DAY.MONTH_ID),‘mm’),‘Mon’)

At this point I am getting the first three characters of each month. Then I created another object using the following:

In the select clause:
to_date(@Select(Calendar\Month Label),‘Mon’)

In the where clause:
@Select(Calendar\Month Label) IN @Prompt(‘Month’,‘A’,‘Calendar\Month Label’,MULTI,CONSTRAINED)

I prompt the user to select the month name(s) and then convert it to a date because the users need a date in the report.

I use the first object as list of values for the second one. But the month names get sorted alphabetically. I tried using the month_id in the list of values and sorting on the month_id. The month names get sorted correctly but i think the users won’t agree on having both the month_id and month_names. They want only the month_name. Is there a want to sort the first object correctly?

Thanks,

Shamnaz

shamnaz (BOB member since 2004-04-21)