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.


   Local Record &rec = CreateRecord(Record.GENL_DEDUCTION);
   &rec.EMPLID.Value = &MCMADSJB.EMPLID.Value;
   &rec.COMPANY.Value = &MCMADSJB.company.value;
   &rec.DEDCD.Value = &MCMADSJB.company.value;
   If &MCMADSJB.effdt.value > &MCMADSMAP.effdt.value Then
      &rec.EFFDT.Value = Date(&MCMADSJB.effdt.value);
   Else
      &rec.EFFDT.Value = Date(&MCMADSMAP.effdt.value);
   End-If;
   &rec.DEDUCTION_END_DT.Value = Date(0);
   &result = &rec.Insert();

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

No comments:

Post a Comment