CODEWITHSUNDEEP
oracle-databasedatepowerbuilder
dpatchery
dpatchery

Reputation: 508

Significance of date 02/31/2157?

I work in a large scale IT support environment. Twice now we have seen an invalid date of 02/31/2157 being inserted in an Oracle DATE column. So far I have not been able to reproduce this problem, but it appears to be happening occasionally when a user attempts to save '00/00/0000' into the column. I believe the value is originating from a PowerBuilder DataWindow update.

The application uses myriad libraries for all sorts of technologies, so this question may be a bit vague, but...

Has anyone seen the date 02/31/2157 in some established library that Oracle could be defaulting to when some other invalid date is entered? Perhaps an end-of-time concept analogous to the beginning-of-time date of 1/1/1970?

Upvotes: 4

Views: 764

Answers (3)

dpatchery
dpatchery

Reputation: 508

Turns out this was a powerbuilder issue. The field was created in the datawindow as required, but was programmatically changed to be non-required before saving. So a null value was being saved to a non-null database column, and powerbuilder inserted some dummy date instead of just throwing an error.

Upvotes: 2

AShelly
AShelly

Reputation: 35520

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

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

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

I'd guess that it is storing either 0x00 or 0xFF in the date bytes, then getting confused when it decodes it. (How does it deal with month 255?)

Upvotes: 4

Hugh Brackett
Hugh Brackett

Reputation: 2706

I remember getting a weird value when saving an invalid date. IIRC it was in PB 9 and we had to get an EBF for it. It was a problem with Date Editmasks and entering an invalid date that wasn't rejected. Sorry I don't have more details.

Upvotes: 0

Related Questions