CA Tech Tip: CA IDMS - SQL date arithmetic with network databases

Discussion created by Eddy_G Employee on Jul 21, 2015
Latest reply on Aug 3, 2015 by Cal_Domingue

CA IDMS Tech Tip by Edward Gorga, Principal Support Engineer for July 21, 2015


Many network databases contain date fields that are stored in two-byte numeric fields – i.e., PIC 9(2). When referencing these fields in SQL statements, when the value is less than 10 then any  leading zero is suppressed and the remaining digit is left-justified. These fields cannot be used in SQL date comparisons and date arithmetic. 


To use these fields in SQL date arithmetic the network data must be translated into a character field in a format SQL will recognize as a valid date.


For detailed instructions on doing this see Technical Document TEC1857929