problem with to_date function

building semantic layer
1

I have a field called ‘Pa Period’ which is a char field. it holds the period in the following format:

JAN-07
FEB-07
…
…
…
AUG-07
etc.

I am trying to convert this to a date with the following code:

to_date(CONCAT('01',  replace(XCCC.XCCC_PROJECTS_TIME_RECORDING.PA_PERIOD,'-',' ')),'dd mmmm yyyy')

When I try to return this field in a WebI report i get an error saying “format code appears twice”.

Any suggestions?

Thanks

foxy :uk: (BOB member since 2007-10-22)

2

Hi,
You have ‘format code appears twice’ error message because the month format is wrong. In Oracle, ‘mm’ is for a month in numeric format (eg. 12). So ‘mmmm’ is ‘mm’ format used twice. For a month trigram, the correct format is ‘MON’.
Moreover, by default, if your date format has no day, the date will be set to the first day of the month. That’s for you can simplify the select statment with:

to_date(XCCC.XCCC_PROJECTS_TIME_RECORDING.PA_PERIOD,'MON-yy')

Regards,

Ghislain

ggouzy :fr: (BOB member since 2005-10-25)

3

I now get the error message “a non numeric character was found where a numeric character was expected.”

foxy :uk: (BOB member since 2007-10-22)

4

Did the error occur with your formula or with mine ?

ggouzy :fr: (BOB member since 2005-10-25)

5

with yours

foxy :uk: (BOB member since 2007-10-22)

6

I have managed to get it to work.

to_date(CONCAT('01 ',replace(XCCC.XCCC_PROJECTS_TIME_RECORDING.PA_PERIOD,'-',' ')),'DD MON YY')

Thanks for your help.

foxy :uk: (BOB member since 2007-10-22)