WIS 10901: ORA-01830 date format picture ends before converting entire input string

ORA-01830 date format picture ends before converting entire input string. (WIS 10901)


Scenarios when I could expect this error:

Convert Date field to other format.

Example
I want to calculate a filter for rolling 12 months. TBL_DIMN_CALN_POST.D_PERD is my date field and I want to filter is for last 12 months.
In BOBJ universe, I am creating a pre-defined filter as
TBL_DIMN_CALN_POST.D_PERD between  to_date(trunc(add_months(sysdate,-12),'MM'),'dd-mm-yy') and  to_date(trunc(sysdate,'MM')-1,'dd-mm-yy')
I am parsing it is everything looks fine.

When I pull the pre-defined object to create a Webi report, it throws error
ORA-01830 date format picture ends before converting entire input string. (WIS 10901)
Possible Reason
Date format has extra data (dd-mm-yyyy HH:mi:ss). We try to convert to a valid date where the first part of the date (dd-mm-yyyy) is required not the remaining data (HH:mi:ss).

In above example, SYSDATE has a format of dd-mm-yyyy HH:mi:ss and we are trying to change it to 'dd-mm-yy'.

Possible Solution
Include the missing Date parts in conversion.

Example
TBL_DIMN_CALN_SERV.D_PERD between  to_date(trunc(add_months(sysdate,-12),'MM'),'dd-mm-yy hh24:mi:ss') and   to_date(trunc(add_months(sysdate,-6),'MM')-1,'dd-mm-yy hh24:mi:ss')

If again, we want to truncate it to “dd-mm-yy” format, we can use to_char function for that.

**Please check List of Error Codes for a detailed list of common errors. **

1 comments:

  1. fabulous information you have shared, I would like to say thanks for you. Please share more valuable content on msbi online course

    ReplyDelete