Eddy_G

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

Outcomes