Date value 02/31/2157?

Question

I work in a large-scale IT support environment. Twice we found an invalid date 02/31/2157 inserted in an Oracle DATE column. So far, I have not been able to reproduce this problem, but sometimes this happens when the user tries to save '00 / 00/0000 'in a column. I believe this value comes from the PowerBuilder DataWindow update.

The application uses many libraries for all kinds of technologies, so this question may be a little vague, but ...

Has anyone seen the date 02/31/2157 in some installed library that Oracle might not be able to execute when some other incorrect date is entered? Perhaps a concept of the end of time similar to the start date of 1/1/1970?

Answer 1

Turns out it was a powerbuilder issue. The field was created in the datawindow window as required , but before saving it was programmatically changed to not required . Therefore, the zero value was stored in the column of the zero database, and powerbuilder inserted some kind of dummy date instead of just throwing an error.

Answer 2

From http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/datatype.htm#i1847 "

Oracle uses its own internal format for storing dates. Date data is stored in fixed-length fields of seven bytes each, corresponding to a century, year, month, day, hour, minute and second.

2157-256 = 1901, which seems suspiciously close to a possible 1/1/1900 era (or 12/13/1901 is the rollover date Problem of 2038 )

I would suggest that it stores 0x00 or 0xFF in date bytes, and then gets confused when decoding it. (As for the month 255?)

Answer 3

I remember that when I save an invalid date, I get a strange value. IIRC it was in PB 9 and we had to get EBF. This was a problem with Date Editmasks and entering an invalid date that was not rejected. Sorry, I do not have more details.