1Date: 10/22/2020    ********************************************************************************       Page:     1
                     *                                CA Datacom SQL                                *
 Time: 20.46.27      *                            DBSQLPR SQL Processor                             *    Version: 15.1
                     *                  Copyright © 1996-2015 CA. All rights reserved.              *
                     ********************************************************************************
0
 Command Line Options
 ____________________

0Option File Options
 ___________________
 AUTHID=SYSUSR
 PAGELEN=2147483647

 -- ----------------------------------------
 -- If the input columns really are NUMERIC:
 -- ----------------------------------------
 INPUT STATEMENT:
 Create table myTable (DATE_CHANGE NUMERIC(8) unsigned not null,
                       TIME_CHANGE NUMERIC(6) unsigned not null);

 ___ SQLCODE=0, SQLSTATE=00000 ___


 INPUT STATEMENT:
 Insert into myTable values(20200822, 70323);

 ___ SQLCODE=0, SQLSTATE=00000, ROWS AFFECTED=1 ___


 -- Answer (1):
 INPUT STATEMENT:
 Select insert(insert(cast(DATE_CHANGE as char(8)),5,0,'-'),8,0,'-')
     || ' ' ||
        insert(insert(cast(TIME_CHANGE as char(6)),3,0,':'),6,0,':')
     as DATETIME_CHANGE
   from myTable;

 DATETIME_CHANGE
 VARCHAR(19)
 ____________________
  2020-08-22 07:03:23
 ___ 1 row returned ___


 -- Answer (2):
 INPUT STATEMENT:
 Select to_date(':' || cast(DATE_CHANGE as char(8))  ||
                cast(TIME_CHANGE as char(6)),':YYYYMMDDHH24MISS')
     as DATETIME_CHANGE
   from myTable;

 DATETIME_CHANGE
 TIMESTAMP NOT NULL
 __________________________
 2020-08-22-07.03.23.000000
 ___ 1 row returned ___


 -- Answer (1), (3):
 INPUT STATEMENT:
 Insert into myTable values(0, 0);

 ___ SQLCODE=0, SQLSTATE=00000, ROWS AFFECTED=1 ___


 INPUT STATEMENT:
 Select case when DATE_CHANGE = 0 then '1900-01-01' else
        insert(insert(cast(DATE_CHANGE as char(8)),5,0,'-'),8,0,'-')
        end || ' ' ||
        insert(insert(cast(TIME_CHANGE as char(6)),3,0,':'),6,0,':')
     as DATETIME_CHANGE
   from myTable;

 DATETIME_CHANGE
 VARCHAR(19)
 ____________________
  1900-01-01 00:00:00
  2020-08-22 07:03:23
 ___ 2 rows returned ___


 -- Answer (2), (3):
 INPUT STATEMENT:
 Select to_date(':' ||
                case when DATE_CHANGE = 0 then '1900-01-01'
                     else cast(DATE_CHANGE as char(8)) end ||
                cast(TIME_CHANGE as char(6)),':YYYYMMDDHH24MISS')
     as DATETIME_CHANGE
   from myTable;

 DATETIME_CHANGE
 TIMESTAMP
 ___________________________
  1900-01-01-00.00.00.000000
  2020-08-22-07.03.23.000000
 ___ 2 rows returned ___


 INPUT STATEMENT:
 drop table myTable;

 ___ SQLCODE=0, SQLSTATE=00000 ___


 -- ----------------------------------------
 -- If the input columns really are CHAR:
 -- ----------------------------------------
 INPUT STATEMENT:
 Create table myTable (DATE_CHANGE CHAR(8) not null,
                       TIME_CHANGE CHAR(6) not null);

 ___ SQLCODE=0, SQLSTATE=00000 ___


 INPUT STATEMENT:
 Insert into myTable values('20200822','070323');

 ___ SQLCODE=0, SQLSTATE=00000, ROWS AFFECTED=1 ___


 -- Answer (1):
 INPUT STATEMENT:
 Select insert(insert(DATE_CHANGE,5,0,'-'),8,0,'-')
     || ' ' ||
        insert(insert(TIME_CHANGE,3,0,':'),6,0,':')
     as DATETIME_CHANGE
   from myTable;

 DATETIME_CHANGE
 VARCHAR(19)
 ____________________
  2020-08-22 07:03:23
 ___ 1 row returned ___


 -- Answer (2):
 INPUT STATEMENT:
 Select to_date(':' || DATE_CHANGE  ||
                       TIME_CHANGE,':YYYYMMDDHH24MISS')
     as DATETIME_CHANGE
   from myTable;

 DATETIME_CHANGE
 TIMESTAMP NOT NULL
 __________________________
 2020-08-22-07.03.23.000000
 ___ 1 row returned ___


 INPUT STATEMENT:
 Insert into myTable values(' ',' ');

 ___ SQLCODE=0, SQLSTATE=00000, ROWS AFFECTED=1 ___


 INPUT STATEMENT:
 Insert into myTable values('       0','     0');

 ___ SQLCODE=0, SQLSTATE=00000, ROWS AFFECTED=1 ___


 INPUT STATEMENT:
 Insert into myTable values('00000000','000000');

 ___ SQLCODE=0, SQLSTATE=00000, ROWS AFFECTED=1 ___


 INPUT STATEMENT:
 Insert into myTable values('20200822',' 70323');

 ___ SQLCODE=0, SQLSTATE=00000, ROWS AFFECTED=1 ___


 -- Answer (1), (3):
 INPUT STATEMENT:
 Select case when translate(DATE_CHANGE,'0',' ') = '00000000'
              then '1900-01-01'
             else insert(insert(DATE_CHANGE,5,0,'-'),8,0,'-')
         end || ' ' ||
        insert(insert(translate(TIME_CHANGE,'0',' '),3,0,':'),6,0,':')
     as DATETIME_CHANGE
   from myTable;

 DATETIME_CHANGE
 VARCHAR(19)
 ____________________
  1900-01-01 00:00:00
  1900-01-01 00:00:00
  1900-01-01 00:00:00
  2020-08-22 07:03:23
  2020-08-22 07:03:23
 ___ 5 rows returned ___


 -- Answer (2), (3):
 INPUT STATEMENT:
 Select to_date(':' ||
                case when translate(DATE_CHANGE,'0',' ') = '00000000'
                      then '1900-01-01'
                     else DATE_CHANGE end ||
                cast(translate(TIME_CHANGE,'0',' ') as char(6)),
                ':YYYYMMDDHH24MISS')
     as DATETIME_CHANGE
   from myTable;

 DATETIME_CHANGE
 TIMESTAMP
 ___________________________
  1900-01-01-00.00.00.000000
  1900-01-01-00.00.00.000000
  1900-01-01-00.00.00.000000
  2020-08-22-07.03.23.000000
  2020-08-22-07.03.23.000000
 ___ 5 rows returned ___


 INPUT STATEMENT:
 drop table myTable;

 ___ SQLCODE=0, SQLSTATE=00000 ___



 =================================================
 == DBSQLPR is completing with return code 0000 ==
 ==                                             ==
 ==         Statements Found:   00019           ==
 ==         Statement Errors:   00000           ==
 ==         Statement Warnings: 00000           ==
 =================================================