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

Would that be like a stored procedure that would be called?
--
Best regards, 

Mike Dreimiller 
Mandis Inc. 
410-526-5996 
Mandis-Inc@xxxxxxx


-------------- Original message from Lisa.Thomas@xxxxxxxxxxx: -------------- 


> 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))) > 
> 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. 
> 
> _______________________________________________ 
> 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. 
>