ORA-01841 full year must be between .....error when order by

[auralaways]

hi experts

     SELECT   porh.segment1,
     CASE
        WHEN popr.action_code = 'APPROVE'
        AND prall.full_name <> 'Sudheer T. Paraputhra'
        AND grdpr.short_name < 'PS-10'
           THEN prall.full_name
     END directman_pr,[QUOTE]to get the dept.head[/QUOTE]
     porh.description, porl.line_num,
     CASE
        WHEN popr.action_code = 'APPROVE'
        AND prall.full_name <> 'Sudheer T. Paraputhra'
        AND grdpr.short_name < 'PS-10'
        AND poph.action_code = 'SUBMIT'
           THEN TO_NUMBER (  TO_CHAR (  TO_DATE ('1', 'J')
                                      + (  poph.action_date
                                         - popr.action_date
                                        ),
                                      'J'
                                     )
                           - 1
                          )
     END days_pr,[QUOTE]number of days from PR approve action by dept.head to PO submit action by user[/QUOTE]
     poha.segment1 po_num, prall.full_name pr_approved_by,
     popr.action_date pr_approved_date,
     CASE
        WHEN poall_apprv.full_name <> 'Sudheer T. Paraputhra'
        AND grdpo_apprv.short_name < 'PS-10'
        AND poph_apprv.action_code = 'APPROVE'
        AND poph.action_code = 'SUBMIT'
           THEN TO_NUMBER (  TO_CHAR (  TO_DATE ('1', 'J')
                                      + (  poph_apprv.action_date
                                         - poph.action_date
                                        ),
                                      'J'
                                     )
                           - 1
                          )
     END days_po,[QUOTE]number of days from PO submit action to po apporve by dept head[/QUOTE]
     popr.sequence_num pr_seq, popr.action_code pr_action,
     poall.full_name po_approved_by, poph.action_date po_approved_date,
     poph.sequence_num po_seq, poph.action_code po_action,
     poph_apprv.action_date po_approved_date_appr,
     poph_apprv.action_code apprv_action_code
FROM po_requisition_headers_all porh,
     po_requisition_lines_all porl,
     po_req_distributions_all prda,
     po_distributions_all poda,
     po_headers_all poha,
     po_action_history popr,
     po_action_history poph,
     hr.per_all_people_f poall,
     hr.per_all_people_f prall,
     hr.per_all_assignments_f asgpo,
     hr.per_all_assignments_f asgpr,
     per_grades_vl grdpo,
     per_grades_vl grdpr,
     po_headers_all poha_apprv,
     po_distributions_all poda_apprv,
     po_action_history poph_apprv,
     hr.per_all_people_f poall_apprv,
     hr.per_all_assignments_f asgpo_apprv,
     per_grades_vl grdpo_apprv
WHERE poph.employee_id = poall.person_id
 AND popr.employee_id = prall.person_id
 AND poall.person_id = asgpo.person_id
 AND prall.person_id = asgpr.person_id
 AND asgpr.grade_id = grdpr.grade_id(+)
 AND asgpo.grade_id = grdpo.grade_id(+)
 AND prda.distribution_id = poda.req_distribution_id
 AND poha.po_header_id = poda.po_header_id
 AND prda.requisition_line_id = porl.requisition_line_id
 AND porh.requisition_header_id = porl.requisition_header_id
 AND porh.requisition_header_id = popr.object_id
 AND poph.object_type_code = 'PO'
 AND poha.po_header_id = poph.object_id
 AND SYSDATE BETWEEN poall.effective_start_date AND poall.effective_end_date
 AND SYSDATE BETWEEN prall.effective_start_date AND prall.effective_end_date
 AND SYSDATE BETWEEN asgpo.effective_start_date AND asgpo.effective_end_date
 AND SYSDATE BETWEEN asgpr.effective_start_date AND asgpr.effective_end_date
 AND SYSDATE BETWEEN poall_apprv.effective_start_date
                 AND poall_apprv.effective_end_date
 AND SYSDATE BETWEEN asgpo_apprv.effective_start_date
                 AND asgpo_apprv.effective_end_date
 AND prda.distribution_id = poda_apprv.req_distribution_id
 AND poha_apprv.po_header_id = poda_apprv.po_header_id
 AND poph_apprv.employee_id = poall_apprv.person_id
 AND poall_apprv.person_id = asgpo_apprv.person_id
 AND asgpo_apprv.grade_id = grdpo_apprv.grade_id(+)
 AND poha_apprv.po_header_id = poph_apprv.object_id
 AND poph_apprv.object_type_code = 'PO'
--- AND PORH.SEGMENT1='6003'
 AND poph_apprv.action_code = 'APPROVE'
ORDER BY 4, 10, 14

if the AND PORH.SEGMENT1 is uncommented i get no error but i need to comment it in order to get the number of days displayed for pr and po

kindly help

[debasisdas]

It is really difficult to understand why ORA-01841 is be raised in your case , without knowing your table structure.

[urtrivedi]

Please be specific about your problem. What problem you are facing? And why you have embedded html code in your query code.

[auralaways]

Please be specific about your problem. What problem you are facing? And why you have embedded html code in your query code.

hi
i m new to this dint know how to post comments:$ which is why i put them as quotes

thanks
the issue is solved :)
it was a julian date issue

changed to
to_date(poph.action_date)-to_date(popr.action_date)

thank u all