II_DATE_CENTURY_BOUNDARY and II_DATE

II_DATE_CENTURY_BOUNDARY and II_DATE_FORMAT

On Aug 19, 11:13, uunet!ingres.com.my!ingres wrote:

> Subject: Dates after the year 1999
>
> I would appreaciate if someone can tell me how to solve the date
> after the year 1999 for systems running on Ingres 6.4 or OpenIngres
> 1.x
>
> What kind of modifications need to be done on the existing
> application.

If you're using 4-digit-year date input, you need to either expand your
input fields to 4 year digits, or set the II_DATE_CENTURY_BOUNDARY
environment variable to a number N.  2 digit years over N are 19NN, else
they are 20NN.  No changes are needed to the database itself if you're
using DATE data.  If you're using integers or characters interpreted as
dates, you're on your own.

-- 
Karl Schendel            Phone: (412) 963-8844
Telesis Computer Corp      Fax: (412) 963-1373
wiz@telesismfg.com
At 6:50 AM +0000 11/17/98, Thana Letchumi wrote:



>Hi,
>
>  I have ingres 6.4/03 running on SCO Open Server 3.0. Currently, the date
>type fields for the tables are kept in yymmdd format. Is Ingres 6.4/03 year
>2000 compliant ?. According to the distributor, it is year 2000 compliant if
>the year is kept in yyyy format. But when I test to enter date in yyyymmdd
>format into the table, it automatically changes back to yymmdd format.

I'm not sure what you mean by the dates being kept in yymmdd format.

If you are using the DATE datatype, DATE *is* Y2K safe.

The problem arises with conversion from DATE to and from user-visible
character form.  Depending on your setting of II_DATE_FORMAT, conversions
like VARCHAR(date-thing) are likely to produce 2-digit years.  Perhaps that
is what you are seeing.  Later releases of Ingres include a new
II_DATE_FORMAT setting which produces 4 digit years.  They also include
II_DATE_CENTURY_BOUNDARY which allows small 2-digit years to be interpreted
as being 2000+x instead of 1900+x.


Karl R. Schendel, Jr.
K/B Computer Associates   schendel@kbcomputer.com
Ingres and Unix Expertise

President, North American Ingres Users Association
president@naiua.org
Join the NAIUA and make your voice heard!





		
> One of our customers is asking whether our Ingres driver
> supports the II_DATE_CENTURY_BOUNDARY setting. I can't find
> this variable in either my 1.2 or 2.0 System Reference Guide.
>
> Anybody knows what this is and where I can find it ?

It's a number used to help kludge applications that can't
handle 2000. Any 2 character year that is > the value gets
1900 added to it, less than the value gets 2000 added to
it (I can't remember offhand what happens to years = value).

It's supported by Ingres 6.4/06, OpenRoad 3.5/03. And as far as I know
all recent patches to OI 1.2 and OI 2. NB that both the client
and server need it set to the same value.

There should be something about it in the release notes.
Similarly for II_DATE_FORMAT=MULTINATIONAL4.

-- 




Extracted from the OI 2.0 manual

Dates After the Year 1999

OpenIngres2.0 provides new features for users who wish to use dates beyond
the year 1999. The valid range for a date field is from 1-Jan-1582 to 
31-Dec-2382 inclusive.

OpenIngres handles date input and output according to the setting of the
environment variable/logical II_DATE_FORMAT.  The settings of this variable
are explained in the SQL Reference Guide which states:

Year defaults to the current year.  In formats that include delimiters
(such as forward slashes or dashes), you can specify the last two digits of
the year; the first two digits default to the current century (1900).  For
example, if you enter '03/21/93' using the format mm/dd/yyyy, the DBMS
assumes that you are referring to March 21, 1993. 

This behavior forces the user to specify all 4 digits of the date when
dealing with dates in the next century.

To modify this behavior, OpenIngres provides a new environment variable
II_DATE_CENTURY_BOUNDARY which may be set with an integer value in the
range 0 < n <= 100.

On date input, if the century part of the date is not specified, (the
date_year component is < 100 and there is a valid setting of
II_DATE_CENTURY_BOUNDARY), then the century is determined by the calculation:

if (date_year < II_DATE_CENTURY_BOUNDARY) then
	(date_year += 2000)
else
	(date_year += 1900)
endif

For example, if II_DATE_CENTURY_BOUNDARY is 90, an input date of '03/21/93'
will be treated as March 21, 1993, but a date of '03/21/03' will be treated
as March 21, 2003.
In addition, a new value for II_DATE_FORMAT "MULTINATIONAL4" is provided. 
This date format is identical to the existing MULTINATIONAL format, with
the exception of the 4 digit year.  Input formats are accepted as for the
MULTINATIONAL format.

Note that if you want to use MULTINATIONAL4 then ALL systems connected must
use it or things start to go wobbly, especially ODBC.

John
Ingres Q & A
Back to William's Home Page
Email William