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. **
fabulous information you have shared, I would like to say thanks for you. Please share more valuable content on msbi online course
ReplyDeleteThanks for such a great article here. I was searching for something like this for quite a long time and at last I’ve found it on your blog. It was definitely interesting for me to read about their market situation nowadays. Well written article Thank You for Sharing with Us pmp training Chennai | pmp training centers in Chenai | pmp training institutes in Chennai | pmp training and certification in Chennai | pmp training in velachery
ReplyDelete