Powerhouse date function bug (long)
Deskin, Bob
Bob.Deskin@Cognos.COM
Wed, 14 Oct 1998 20:36:55 -0400
This is absolutely, positively NOT a bug. This is the way the PowerHouse
DATE function was designed and for very good reasons as I'll describe
shortly. Please read the article mentioned in a later posting that's on the
web site. It has the "goods" on PowerHouse date processing and functions.
Please keep in mind that if you use a dictionary option of century excluded,
then all century excluded dates are assumed to be within the default
century. And the results of the DATE and LASTDAY functions are century
excluded dates (which must fall within the default century). Here are all
the gory details promised earlier for those who really want them.
The DATE Function Issue
The DATE function takes a number of days from a base date and returns a
date. By default the base date is Dec 31, 1899. There is an option to
specify a different base date on OpenVMS series 7 and on all series 8
versions. The rest of this discussion assumes Dec 31, 1899. The date
returned is century included or excluded based on the dictionary CENTURY
INCLUDED|EXCLUDED system option.
The complaint is that if your dictionary system option is CENTURY EXCLUDED,
then any date returned outside of the DEFAULT CENTURY results in a data
conversion error. For example:
Dictionary SYSTEM OPTION # of days DEFAULT CENTURY Result
CENTURY EXCLUDED 0-36524 19 Jan
1, 1900 - Dec 31, 1999
36525 - 73049 19 data
conversion error
0 - 36524 20 data
conversion error
36525 - 73049 20 Jan
1, 2000 - Dec 31, 2099
< 0 19 or 20
data conversion error
> 73049 19 or 20
data conversion error
CENTURY INCLUDED 0 - 73049 19 or 20
Jan 1, 1900 - Dec 31, 2099
Discussion
The result of the DATE function is a century included or excluded date based
on the dictionary system option.
As the chart shows, if the dictionary option is CENTURY INCLUDED, a date is
returned regardless of the days value. The date returned is century
included. To be precise, the possible date range is Jan 1, 100 to Dec 31,
9999 since PowerHouse does not support a date with century 0.
If the dictionary option is CENTURY EXCLUDED, PowerHouse assumes that all
century excluded dates, or date expressions with a 0 century, are within the
DEFAULT CENTURY. If the result of the DATE function is outside of the
DEFAULT CENTURY, PowerHouse assumes that it is an invalid date and issues a
data conversion error.
PowerHouse assumes century excluded dates are within the default century in
3 other ways:
* If a century excluded date or a 0 century value is encountered in a
date expression, PowerHouse adds the default century before processing the
expression through the function. This applies to the DATEEXTRACT, DAYS,
DECIMALTIME, and LASTDAY functions. This was done so that we would have one
set of functions rather than one set for century included and one set for
century excluded.
* When entering century excluded dates in QUICK or QUIZ and QTP PARMs,
PowerHouse checks to ensure that the date is valid for the century. The only
effect is that Feb 29 is valid if the default century is 20 but invalid if
it's 19. If a century excluded date has a century included date format,
PowerHouse ensures that the century entered is the default century.
* When displaying century excluded dates with century included date
formats, PowerHouse uses the default century.
Note that none of these will change in 7.10G and 8.1x (with or without the
input century window feature).
What If?
Now let's look at what would happen if we changed the function such that the
DATE function would produce a date value even if the number of days put the
result outside of the default century. Assume that the default century is 19
and that the system option is left as CENTURY EXCLUDED.
* Storing the date results in dates that are within 2 centuries.
Presumably the existing data is within century 19. Now there's data within 2
centuries and nothing in PowerHouse to indicate that.
* Using the date in a date function results in the default century
being added which gives an incorrect date. Plus the fact that if the value
is 000229, 19000229 is an invalid date.
* Displaying the date with a century included date format gives the
wrong century for the date.
Without changing more of PowerHouse, the workarounds for the above are to
add windowing logic wherever century excluded dates are used and/or to use
the new CENTURY function to create a temporary or defined item with the
correct century. Note that windowing only works where the range of dates is
within 100 years.
The point is that simply changing how the DATE function works does not
resolve the issues of using century excluded dates and potentially leads to
data integrity issues.
We extensively investigated adding full windowing to PowerHouse but rejected
it for a number of reasons:
* The extensive changes needed were potentially destabilizing to
existing functionality that has worked for many years.
* There was a potential performance penalty for those who didn't need
windowing.
* None of the ideas put forward would solve the problem of comparisons
and assignments with literals or numeric expressions. These would still have
to be found and changed manually. Since the point of implementing windowing
was to avoid code searches and changes, the fact that it would still be
required vastly reduces the benefit of implementing windowing.
* There would still be collating and sequencing issues including
sorting, index retrieval, and TurboIMAGE sorted chains.
* If the application were originally written as century aware and year
2000 compliant, the changes wouldn't be used or needed.
We did implement the CENTURY function that provides the ability to determine
the century based on the year.
Bob Deskin
Senior Product Advisor bob.deskin@cognos.com
Cognos Inc. (613) 738-1338 ext 4205 FAX: (613) 228-3149
3755 Riverside Drive P.O. Box 9707 Stn. T, Ottawa ON K1G 4K9 CANADA
> ----------
> From: Robert J.M. Edis[SMTP:Robert.Edis@creatcomp.com]
> Sent: October 14, 1998 3:49 PM
> To: 'powerh-l@lists.swau.edu'
> Subject: Powerhouse date function bug
>
> G'day good people
>
> There is a bug in PowerHouse that's causes a DCE when using the DATE()
> function and the PHD is CENTURY EXCLUDED. If the function will result
> in a date after 12/31/1999 the Data Conversion Error occurs.
>
> I had a look at Cognos's web site knowledge base but the closest
> reference I could find says this occurs only with the date 02/29/2000.
> We have tested this issue with 7.29C8, 7.09 and 6.20E with the same
> result in each case. Here is an example of code:
>
> DEFINE date1 DATE [CENTURY INCLUDED] = PARM [FORMAT MMDDYYYY]
> DEFINE days1 = DAYS(date1) - 5
> DEFINE date2 DATE [CENTURY INCLUDED] = DATE(days1)
> REPORT date1 FORMAT MMDDYYYY date2 FORMAT MMDDYYYY
>
> Note: syntax in [ ] is optional and makes no difference to outcome.
>
> Input values: 10141998, 12311999, 01012000, 01052000, 01062000
>
> Results:
> Date1 Date2
> 10/14/1998 10/09/1998
> 12/31/1999 12/26/1999
> 01/01/2000 12/27/1999
> 01/05/2000 12/31/1999
> 01/06/2000 DCE
>
> Has anyone out there found a work around to this problem other than
> changing the PHD to CENTURY INCLUDED?
>
> Robert "Blue" Edis
> Creative Computing Inc.
> Rhode Island, USA
>
>
>
>
> = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
> =
> Subscribe: "subscribe powerh-l" in message body to
> majordomo@lists.swau.edu
> Unsubscribe: "unsubscribe powerh-l" in message to majordomo@lists.swau.edu
> powerh-l@lists.swau.edu is gatewayed one-way to bit.listserv.powerh-l
> This list is closed, thus to post to the list, you must be a subscriber.
>
= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
Subscribe: "subscribe powerh-l" in message body to majordomo@lists.swau.edu
Unsubscribe: "unsubscribe powerh-l" in message to majordomo@lists.swau.edu
powerh-l@lists.swau.edu is gatewayed one-way to bit.listserv.powerh-l
This list is closed, thus to post to the list, you must be a subscriber.