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

[santhu1]

when i write a query and execute the query it gives error.

My Query

SELECT NI.POLICY_HOLDER_SALUTATION ||''||PD.DRAWER_NAME AS DRAWER_NAME,
PD.ADDRESS_LINE_1 || NVL2(PD.ADDRESS_LINE_1,',','') || PD.ADDRESS_LINE_2 || NVL2(PD.ADDRESS_LINE_2,',','') || PD.ADDRESS_LINE_3 || NVL2(PD.ADDRESS_LINE_3,',','') || PD.CITY || NVL2(PD.CITY,',','') || PD.PINCODE || NVL2(PD.PINCODE,',','') || CHR(10) || PD.STATE AS DRAWER_ADDRESS,
PD.DRAWER_CONTACT_NO AS DRAWER_CONTACT_NO,
TO_CHAR(PD.LOGIN_DATE,'DD-Mon-YYYY') AS LOGIN_DATE ,
NVL((CASE WHEN PAYMENT_MODE IN ('Cheque','Demand Draft') THEN TO_CHAR(PD.INSTRUMENT_DATE,'DD-Mon-YYYY')
     WHEN PAYMENT_MODE IN ('Cash','OTC Yes Bank') THEN ''
     ELSE TO_CHAR(PD.LOGIN_DATE,'DD-Mon-YYYY') END),' ') AS INSTRUMENT_DATE ,
PD.USER_BRANCH_NAME,
PD.RECEIPT_NUMBER,
TO_CHAR(PD.AMOUNT,'999999999.99') AS AMOUNT ,
NVL(PD.POLICY_STATUS,' ') POLICY_STATUS,
NVL((CASE WHEN PD.PAYMENT_MODE IN ('Cash','OTC Yes Bank') THEN NULL ELSE PD.INSTRUMENT_NUMBER END),' ') AS INSTRUMENT_NUMBER,
NVL(PD.DRAWER_BANK,' ') AS DRAWER_BANK ,
PD.CONTRACT_NUMBER,
NVL(PD.EMAIL_ID,' ') EMAIL_ID,
NVL(PD.PLAN_NAME,' ') PLAN_NAME,
NVL(PD.PREMIUM_FREQUENCY,' ') PREMIUM_FREQUENCY,
TO_CHAR(PD.INSTRUMENT_DATE,'DD-Mon-YYYY'),
PD.CHEQUE_RECEIVE_DATE, TRIM(f_words(PD.AMOUNT)) AS AMOUNT_WORDS_OLD ,
 UPPER((TO_CHAR (TO_DATE (PD.AMOUNT, 'j'), 'jsp') )) AS AMOUNT_WORDS FROM PAYMENT_DETAILS PD, NEWBUSS.NB_INWARD NI 

error while executing

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

highlighted part gives error.

reply soon.
thanks in advance.

[Luuk]

SQL> select to_date(1,'j') from dual;
 
TO_DATE(
--------
01-01-12
 
SQL> select to_date(1.3,'j') from dual;
select to_date(1.3,'j') from dual
               *
FOUT in regel 1:
.ORA-01830: ......

There should be NO decimals in PD.AMOUNT....

[santhu1]

Hi Luuk,

Thank you. it's working fine.

[santhu1]

Hi Luuk,

if there is decimals in that PD.AMOUNT how we need to write this.

[santhu1]

Hi Luuk,

can u help me in this

SELECT    TO_CHAR (TO_DATE (TRUNC (PD.AMOUNT), 'J'), 'JSP')
       || ' Point '
       || TO_CHAR (TO_DATE (TO_NUMBER (SUBSTR (PD.AMOUNT , INSTR (PD.AMOUNT, '.') + 1)),'J'),'JSP')
  FROM PAYMENT_DETAILS PD ; 

when i excute this for decimal values, it gives the result for every value in the amount field. i want the result for only decimal values. if non decimal values it print as it is.

[Luuk]

you could use: CASE WHEN .... THEN ..... END:

SELECT    TO_CHAR (TO_DATE (TRUNC (PD.AMOUNT), 'J'), 'JSP')
       || ' Point '
       || CASE WHEN PD.AMOUNT<>TRUNC(PD.AMOUNT) THEN
       TO_CHAR (TO_DATE (TO_NUMBER (SUBSTR (PD.AMOUNT , INSTR(PD.AMOUNT, '.') + 1)),'J'),'JSP')  END
  FROM PAYMENT_DETAILS PD ; 

[santhu1]

Hi Luuk,

Finally, I got exact OutPut

  TO_CHAR (TO_DATE (TRUNC (PD.AMOUNT), 'J'), 'JSP') 
          || CASE WHEN PD.AMOUNT<>TRUNC(PD.AMOUNT) THEN
    ' Rupees '||    TO_CHAR (TO_DATE (TO_NUMBER (SUBSTR (PD.AMOUNT , INSTR(PD.AMOUNT, '.') + 1)),'J'),'JSP') || ' Paise' else ' Rupees Only'  END

Thanks for ur support.