Tuesday, January 14, 2014

Null date in a Record


Today I was trying to create a new record that had a EFFDT and one other date field that needed to be NULL.  During the update oracle failed to identify null as a date and gave the error "ORA-01841: (full) year must be between -4713 and +9999, and not be 0".  The solution was to not set the date field to null but instead use Date function with the value 0.


  1. Local Record &rec = CreateRecord(Record.GENL_DEDUCTION);
  2. &rec.EMPLID.Value = &MCMADSJB.EMPLID.Value;
  3. &rec.COMPANY.Value = &MCMADSJB.company.value;
  4. &rec.DEDCD.Value = &MCMADSJB.company.value;
  5. If &MCMADSJB.effdt.value > &MCMADSMAP.effdt.value Then
  6. &rec.EFFDT.Value = Date(&MCMADSJB.effdt.value);
  7. Else
  8. &rec.EFFDT.Value = Date(&MCMADSMAP.effdt.value);
  9. End-If;
  10. &rec.DEDUCTION_END_DT.Value = Date(0);
  11. &result = &rec.Insert();

The Date(0) will correctly make the database field null.

No comments:

Post a Comment