RE: DB2 SQL and MAPICS Dates (100+YY)MMDD

You could create an SQL User defined function that masks the complexity.

-----Original Message-----
From: mandis-inc@xxxxxxx [mailto:mandis-inc@xxxxxxx] 
Sent: Thursday, September 22, 2005 2:55 PM
To: MAPICS-L@xxxxxxxxxxxx
Subject: [MAPICS-L] DB2 SQL and MAPICS Dates (100+YY)MMDD

MAPICS holds dates in the (100+yy)MMDD format.  To convert the MAPICS date
format to an an sql date format I am using this statement: SELECT
DATE(concat(concat(concat(substr(char((C6ALDT-1000000)+20000000),1,4), '-'),
concat(substr(char((C6ALDT-1000000)+20000000),5,2), '-')),
substr(char((C6ALDT-1000000)+20000000),7,2))) FROM MBC6REP.  Lengthly, but
it works.  

To select records by comparing dates, I am using a statement such as: SELECT
DATE(concat(concat(concat(substr(char((C6ALDT-1000000)+20000000),1,4), '-'),
concat(substr(char((C6ALDT-1000000)+20000000),5,2), '-')),
substr(char((C6ALDT-1000000)+20000000),7,2))) FROM MBC6REP  WHERE
DATE(concat(concat(concat(substr(char((C6ALDT-1000000)+20000000),1,4), '-'),
concat(substr(char((C6ALDT-1000000)+20000000),5,2), '-')),
substr(char((C6ALDT-1000000)+20000000),7,2))) <CURRENT_DATE

While this sql select statement works, there must be a simpler way.  I would
appreciate any and all comments/suggestions.

In advance, thanks
--
Best regards, 

Mike Dreimiller 
Mandis Inc. 
410-526-5996 
Mandis-Inc@xxxxxxx
_______________________________________________
This is the MAPICS ERP System Discussion (MAPICS-L) mailing list
To post a message email: MAPICS-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/mapics-l
or email: MAPICS-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/mapics-l.