Ian_Hill

CA Tuesday Tip: (CA IDMS) Converting non-SQL date/time fields to SQL types

Discussion created by Ian_Hill Employee on Sep 4, 2012
CA IDMS Tuesday Tip by Ian Hill, Principal Support Engineer for September 4, 2012

If you have a non-sql database with date and times which are of the format, for example, PIC 9(8) and PIC 9(6), you can use the following SQL to return those values in the relevant SQL data type:

SELECT ID,
DATE(CAST(INSERT(INSERT(CAST(NAVDATE AS CHAR(8)), 7, 0, '-'),
5, 0, '-') AS CHAR(10))) AS SQLDATE,
TIME(CAST(INSERT(INSERT(SUBSTR(CAST(NAVTIME+1000000 AS CHAR(7)),
2, 6), 5, 0, ':'), 3, 0, ':') AS CHAR(8))) AS SQLTIME
FROM SNSSCHM.NAVDTS;
*+
*+     ID  SQLDATE     SQLTIME
*+     --  -------     -------
*+      1  2009-12-03  14.12.34
*+      2  1999-12-03  08.06.07
*+
*+ 2 rows processed

See Knowledge base article TEC505610 for some more detailed suggestions on this topic.

Outcomes