Lab Note #10: A0 Dates in SQL

Many people want to access data they have stored in Image/SQL databases via industry standard reporting tools
and utilities, for example, Microsoft Excel via ODBC. The eRP suite of applications store dates as characters in
your Image databases. The Y2K solution that has been provided introduced "A0" dates (see your YEAR2000
document on the HP3000).

In the past, accessing these records could cause some confusion, as sequencing can be effected by this format.
HP recently enhanced Image/SQL to interpret this dating format to return a more consistent and usable result, that
is, to return dates in more common dating format.

Lets look at some data through ISQL, the way that it has reacted previous to the change.

:ISQL
isql=> connect to 'mmiidbe.hpmm2sql';
isql=> select part_number, date_added, date_changed,
> date_obsolete from pcatdb.item_data;

select part_number, date_added, date_changed, date_obsolete from pcatdb.ite
------------------+----------+------------+-------------                   
PART_NUMBER       |DATE_ADDED|DATE_CHANGED|DATE_OBSOLETE                   
------------------+----------+------------+-------------                   
102-08            |970710    |            |
300-06            |970805    |970805      |
201-14            |970710    |970806      |
202               |970805    |            |
CINEMASCOPE LENS  |A00301    |A00301      |A70707
100-08            |970710    |            |
201-06            |970710    |970806      |
---------------------------------------------------------------------------
First 16 rows have been selected.
U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>, or e[nd] > e
isql=> exit;
:

Note the "A0" dates in the database - see part "CINEMASCOPE LENS".

Reading from Communicator 3000 MPE/iX Release 6.5 E0300, page 164-169, there is now a patch available that
provides date mapping for "A0" dates. As of this writing it is patch ATCLXH6A for MPE/iX 6.5. This patch installs
IMAGE/SQL B.G4.05 (program says B.G4.02; Not available for MPE 6.0). In IMAGESQL you can update an eRP
X6 datetype to an SQL datetype.

After you make these IMAGESQL changes, your ODBC connections should see these fields as dates.
This example is using the eRPAM (MNT) application and has the MPE patch installed.

:IMAGESQL
HP36385B B.G4.02               IMAGE/SQL Utility   THU, MAY 10, 2001,  2:41 PM 
(C) COPYRIGHT HEWLETT-PACKARD COMPANY 1993

>>set dbe mmiidbe
>>set base pcatdb.pub
>>di map

ATTACHED BASES   : PCATDB.PUB.ERPMNT
DBEnvironment    : MMIIDBE.HPMM2SQL.ERPMNT
Owner Name       : PCATDB

MAPPED(SOURCE)   SOURCE             MAPPED         SOURCE     MAPPED 
   TABLE         FIELD              COLUMN          TYPE       TYPE       NOTES
------------ ---------------- -------------------- ------- -------------- -----
ITEM_DATA (ITEM-DATA)                                                          
             . . .
             DATE-ADDED       DATE_ADDED           X6      CHAR(6)        U 
             DATE-CHANGED     DATE_CHANGED         X6      CHAR(6)        U 
             . . .
             DATE-OBSOLETE    DATE_OBSOLETE        X6      CHAR(6)        U 
             . . . 

NOTES:
  S: Source field has been split
  U: Source field type has been updated

>>update type in item_data.date_added format=dt35, '------', '++++++' to date
Updated information in table ITEM_DATA.
>>update type in item_data.date_changed format=dt35, '------', '++++++' to date
Updated information in table ITEM_DATA.
>>update type in item_data.date_obsolete format=dt35, '------', '++++++' to date
Updated information in table ITEM_DATA.
>>di map
. . .
ITEM_DATA (ITEM-DATA)                                                          
             . . .
             DATE-ADDED       DATE_ADDED           X6      DATE           UF
              FORMAT: DT35,'------','++++++'                                   
             . . .
>>exit
:
:isql
isql=> connect to 'mmiidbe.hpmm2sql';
isql=> select part_number, date_added, date_changed, date_obsolete
> from pcatdb.item_data;

select part_number, date_added, date_changed, date_obsolete from pcatdb.ite
------------------+----------+------------+-------------                   
PART_NUMBER       |DATE_ADDED|DATE_CHANGED|DATE_OBSOLETE                   
------------------+----------+------------+-------------                   
102-08            |1997-07-10|9901-12-31  |9901-12-31
300-06            |1997-08-05|1997-08-05  |9901-12-31
201-14            |1997-07-10|1997-08-06  |9901-12-31
202               |1997-08-05|9901-12-31  |9901-12-31
CINEMASCOPE LENS  |2000-03-01|2000-03-01  |2007-07-07
100-08            |1997-07-10|9901-12-31  |9901-12-31
201-06            |1997-07-10|1997-08-06  |9901-12-31
---------------------------------------------------------------------------
First 16 rows have been selected.
U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>, or e[nd] > e
isql=> exit;
:

As noted in the Communicator, invalid dates, like all the blank fields, appear as 9901-12-31. If the low or high
values specified in the IMAGESQL update command are used when adding or changing dates, then these values
will appear as 9900-01-01 and 9999-12-31 respectively.


Lab Note #10: A0 Dates in SQL, 31 Aug 2001 Keven Miller
Feedback and topic suggestions are welcome and can be sent to technote@exegesys.com.

Lab Notes are hints & technical notes from the "Labyrinth" (the eXegeSys software lab); so named because of our complex
mission to assimilate understanding the eRP product suite, it's source code, and our development procedures.