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,<blockquote>to get the dept.head</blockquote>
         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,<blockquote>number of days from PR approve action by dept.head to PO submit action by user</blockquote>
         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,<blockquote>number of days from PO submit action to po apporve by dept head</blockquote>
         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