Baan null date field view from oracle sql

zaidlaz · #1 19th September 2001, 15:05

Hi Guys,

Baan date fields are known to be "NOT NULL". If were enter null date in a baan table and viewed it using oracle it will be shown as '01-JAN-99'. How does baan allows us to enter a null date value in a not null date field? How can I perform an insert of a null date from sqlplus? Appreciate if anyone could enlighten this phenomena. Thank you.

Best Wishes,
Zaid

gfasbender · #2 19th September 2001, 19:32

Baan uses 01-01-9999 as their NULL date in Oracle. So,

insert into baan.ttadvxxx(..., t$date, ...)
values(..., to_date('01-01-9999', 'mm-dd-yyyy'), ...);

gguymer · #3 21st September 2001, 20:51

Oracle will not let you insert the date '01/01/-9999' using SQLPlus or PL/SQL. Oracle will return: ORA-01841: (full) year must be between -4713 and +9999. I asked Oracle support how they thought Baan was getting this date into Oracle and they believed that it was being done through OCI since it doesn't validate dates. There is a flag that can be set in the db_resource file of Baan that will change the Baan NULL date to -4713 instead of -9999, but you would have to convert all date fields over to that value before doing that. The only way I got around this was to create a Baan table with one row in it. That row contains a Baan NULL date that I had Baan populate. Oracle will let you copy the NULL date from that table into the date field of any table you want. Hope this helps.

patvdv · #4 21st September 2001, 22:11

The OCI call interface lets Baan get away with more things than just the NULL date. Another example is the current problem with negative document numbers (t$docn) in tfgld018 on Oracle8 installations. See also: http://www.baanboard.com/baanboard/s...s=&threadid=10

JamesV · #5 21st September 2001, 22:25

Baan does make an OCI call in order to insert the binary date into the field. This has caused some problems for me when doing Oracle replication projects, but we were able to work around the problem.

The methodology described is also correct as far as correcting the problem.

Here are some details from the U7076BUS documentation of the Baan Oracle Database driver:

Driver resource: ora_date
Environment variable: ORA_DATE
Client/Server resource: Set for server only
Type: Integer
Default: 1
Description
This resource indicates the valid date range used by
the Oracle driver. If set to 1 (default) the range is from
01 Jan. 0001 to 31 Dec. 4712. If set to 0, the range will
be 01 Jan. 9999 BC to 31 Dec. 9999. It is
recommended that the default be used; Driver resource ora_date
Environment variable ORA_DATE
Client/Server resource Set for server only
Type Integer
Default 1
Description This resource indicates the valid date range used by
the Oracle driver. If set to 1 (default) the range is from
01 Jan. 0001 to 31 Dec. 4712. If set to 0, the range will
be 01 Jan. 9999 BC to 31 Dec. 9999. It is
recommended that the default be used; the range
allowed with the value 0 will not be supported in future
BaanERP releases because the date range cannot be
handled by non-Baan programs.

-------------------------

It is important to eliminate the old date format as ERP 5.0c no longer supports the old -9999 BC start of epoch. It also does not use the OCI binary date insert. So, if you are on ERP get rid of it.
If you are on Baan IV, you can live with it depending on your use of advanced Oracle features or third-party applications.

-- Jim

gfasbender · #6 22nd September 2001, 01:14

I said "Baan uses 01-01-9999 as their NULL date in Oracle." Where did all this 01/01/-9999 come from?

zaidlaz · #7 23rd September 2001, 03:14

I've create a table with one field as select from the baan table field which contains the null date presumel seen as '01-JAN-9999'.
Alter the PL/SQL procedure to select from this table. It worked perfectly!

High Five! to all who contirbuted to this.

Best Wishes,
Zaid

gguymer · #8 24th September 2001, 16:23

Quote:

Originally posted by gfasbender
I said "Baan uses 01-01-9999 as their NULL date in Oracle." Where did all this 01/01/-9999 come from?

It is meant to highlight the fact that the Baan NULL date in Oracle has a negative year. You can format dates any way you want, but yours does not show that the date is negative. Your format uses a dash as a separator, and mine uses a foward shash as a separator with a dash next to the year indicating it as a negative number. If you will use this Oracle format mask, 'MM/DD/SYYYY' in the TO_CHAR funtion, it will display the date the same as I orignally posted it showing the sign of the year.

gurmeetmakkar · #9 19th June 2006, 09:43

How to find out the Null date from the database

Gurmeet Singh

PeterM · #10 21st June 2006, 18:13

I know the following two possibilities to compare such dates in Oracle:

select ...
from ttimjr913...
where T$exdat < to_date('01.01.0001','DD.MM.YYYY')

or

select ...
from ttimjr913...
where T$exdat = least(T$exdat)