Re: Invalid Date, 61-APR-2003, found in Oracle Date Field

"Connor McDonald" <connor_mcdonald_at_yahoo.com> wrote in message news:3EB095C6.5D84_at_yahoo.com...
| Peter wrote:
| >
| > RDBMS Version: 8.1.6.3
| > Operating System and Version: Solaris 2.7
| >
| > Invalid Date, 61-APR-2003, found in Oracle Date Field
| >
| > One of our application users recently reported an invalid date that was
| > stored in two of their tables. The date was 61-APR-2003 and it was in
two
| > different tables which led them to believe that it had been transferred
from
| > one table to another or they had both been inserted at the same time
since
| > both dates shared a common employee number.
| >
| > I can't think of anyway that the database would allow an invalid date to
be
| > entered into a date field. However, I did find this in the Oracle Call
| > Interface Programmer's Guide under External Data types.
| >
| > "When you enter a date in binary format using the DATE external
datatype,
| > the database does not do consistency or range checking. All data in this
| > format must be carefully validated before input."
| >
| > I'm not sure if the above statement means that you can enter invalid
dates
| > into Oracle's internal DATE datatype since it is an external datatype.
| >
| > Lastly, I realise that all 8.1.6 databases should be upgraded to at
least
| > 8.1.7, but due to application complications, this cannot be done yet.
|
| What its saying is that dates are basically just 7 bytes. If you use
| some means (eg OCI) to simply dump any old values into those 7 bytes you
| could concoct any old 'date' value
|
| hth
| connor
| --
| =========================
| Connor McDonald
| http://www.oracledba.co.uk
|
| "Some days you're the pigeon, some days you're the statue"

Ok, so what you are saying (I think) is that I can transform the date '61-APR-2003' into binary format and then send that off to an oracle database via OCI and this will be stored in a table as an internal DATE field and Oracle won't complain one little bit?

Then, I can simply query this table via SQLPLUS and it will show up as '61-APR-2003' without any problems. Does this then mean that I could then run a

CREATE TABLE NEW_DATE_TABLE AS SELECT * FROM BAD_DATE_TABLE; and the invalid date would be happily transferred to the new table without any checking? I would try this, but I don't know anything about OCI.

Thanks Received on Wed Apr 30 2003 - 23:06:30 CDT