Re: DBCENTURY and 4GL
QueriX 4GL correctly interprets DBCENTURY and it is RUNTIME FREE.
It is Compatible with Informix 4GL Compiler so you do not have to
re-write your code. It also boosts the calculations in your
programs.
Jonathan Leffler wrote:
}
} On Wed, 12 Nov 1997, Jacob Salomon wrote:
} > I have a question regarding the much-hyped DBCENTURY environment
} > variable. I understand that the 7.2 engine uses it to help
} > interpret a 2-digit year. This is fine if I have a statement in
} > dbaccess like:
} >
} > insert into orders(customer, order_date)
} > values(12345, "01/15/03")
} >
} > If DBCENTURY happens to be "C" and today's date is 11/12/1996, the
} > above date will be interpreted as 01/15/2003. (Assuming, of
} > course, that I have correctly interpreted the Guide to SQL -
} > Reference).
}
} Yes, the engine will interpret that correctly without any particular
} help from DB-Access. If you wrote the same statement in I4GL and
} executed it, you would get the same effect because the engine is
} converting the string into a date, and not I4GL.
}
} > However, no application I know uses dbaccess directly.
}
} True, in general.
}
} > It is the 4GL app that gets the date in from a form. When the
} > INPUT statement is executed, the string "01/15/03" the user typed
} > in the date field is already stored in binary (days since
} > 12/31/1899).
}
} Correct; here, the I4GL runtime code is translating the string into
} a DATE before the value is sent to the engine.
}
} > I am certain that the INPUT statement will have interpreted the
} > "01/15/03" as the year 1903, not 2003, since (as far as I know)
} > 4GL knows nothing about DBCENTURY.
}
} Well, it depends on which version you're using, but in general you
} are correct. All versions which are not DBCENTURY-aware will indeed
} convert the entered 2-digit string into a date in 1903, and never
} into 2003.
}
} As I understand it, version 4.20 I4GL is available and supports
} DBCENTURY. The corresponding 6.10 release is not yet available
} AFAIK.
}
} > Is this indeed the state of affairs with 4GL? This is highly
} > relevant, because we are getting close to the situation where
} > people will be entering due dates past 12/31/1999.
}
} I'll reiterate my advice; always, but always, display all 4 digits
} of the year. If the user is looking, they may notice that the date
} has been mistranslated. If only 2 digits are shown, the user
} doesn't know that the date has been misentered. Note that this
} applies even with DBCENTURY aware products; if DBCENTURY is set to F
} and the user enters 96, it will be entering 2096, not 1996, and
} unless you show all 4 digits of the year, the user won't know!
}
} And don't forget that you could probably put a check constraint on
} most DATE columns, such as:
}
} CREATE TABLE ...
} (
} ...
} SomeDate DATE NOT NULL
} CHECK (SomeDate >= MDY(1, 1, 1970)) CONSTRAINT
} cN_datecheck,
} ...
} )
}
} You can adjust the criteria appropriately, using some small set of
} control dates. You might also want to apply upper bounds, too, or
} instead. Eg:
}
} BirthDate DATE NOT NULL
} CHECK (BirthDate BETWEEN MDY(1, 1, 1880) AND MDY(12,
} 31, 2010)) CONSTRAINT cN_datecheck,
}
} These ranges could be computed, I think:
}
} SomeDate DATE NOT NULL
} -- Check that range is within 10 years (approx) of
} today CHECK (SomeDate BETWEEN TODAY - 10 * 365 AND
} TODAY + 10 * 365) CONSTRAINT cN_datecheck,
}
} However, be wary of this... If the user subsequently does an update
} of the column even without changing the value in the column (eg
} UPDATE Table SET * = (r_table.*) WHERE ... in I4GL), then the
} constraint may be violated because enough time has elapsed since the
} data was entered validly to place the date out of range, thus
} causing the update to be rejected. You could use an INSERT trigger
} instead, but the declarative constraint is generally clearer, and
} probably quicker (though I have no empirical evidence to justify
} that claim).
}
} Yours,
} Jonathan Leffler (johnl@informix.com) #include <witticism.h>
Best Regards
-------------------------------------------------------
Querix (UK)Ltd | Email:info@querix.co.uk
10 Bellevue Road | Voice:+44 (0) 1703 232345
Southampton SO15 2AY | Fax :+44 (0) 1703 399685
UK | URL :http://www.querix.co.uk
-------------------------------------------------------