SQL command to convert Datacom numeric type to date, datetime type

[THANG TRAN]

Hello,
I am using SQL language (not Dataquery) to convert Datacom numeric type to string/date/datetime type in a Select clause.
More specifically, I have two NUMERIC columns: DATE_CHANGE of format yyyymmdd and TIME_CHANGE of format hhmmss and I want to combine those two numbers into a  string and a datetime type.
For example: I have 20200822 and 070323,

1) how to add '-' and space ' 'and combine those two columns to get '2020-08-22 07:03:23'?
something like:
select insert(DATE_CHANGE, 5, '-') + insert(TIME_CHANGE, 3, ':') as DATETIME_CHANGE
from myTable

2) how to combine those two columns to a datetime type?
something like:
select cast((cast(DATE_CHANGE as date), cast(TIME_CHANGE as time), datetime) as DATETIME_CHANGE
from myTable

3) The requirement in my real problem also include:
- if DATE_CHANGE is 0, convert it to 1900-01-01
- if TIME_CHANGE number has less than 6 digits, padding 0s to the left and convert it to string/time as above

Can you please give me a SQL command to cover both (1), (3) and (2), (3)?
Thank you very much.

[Owen Williams]

I am slightly confused. You state that your input columns are NUMERIC datatype, but then in question 3 you state that the TIME_CHANGE column can have less than 6 digits, which is not possible with the NUMERIC (or DECIMAL/INTEGER) datatype. The leading zeros would always be there. This leads me to suspect that your input columns might actually be CHAR datatype, where leading blanks might occur.

Anyway I have tested scripts for both datatypes and the results are below. Note that these scripts make use of the latest scalar function enhancements that are only available if you have applied the latest published maintenance for Datacom 15.1. 

Date: 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. * ******************************************************************************** Command Line Options ____________________ Option 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 == =================================================​
You may notice that I prepend the to_date parameters with ":" (for both the DATETIME column and the template). This should not be necessary, but currently it is - due to a bug in the to_date function. I will raise a case with support to get that fixed, but the above workaround will bypass that bug.

------------------------------
Technical Consultant
Dixons Carphone
------------------------------

[Owen Williams]

Hmmm! The code formatting went haywire, so I have uploaded the output as a text file attachment instead.

------------------------------
Technical Consultant
Dixons Carphone
------------------------------

Attachment(s)

Thang Tran.txt   5 KB 1 version

[THANG TRAN]

Thank you Owen very much.
The last time you helped me in using "table" keyword and now this time with a full solution!!!
Way more than expected!!!