Turn decimal into timestamp

1

Hello, I'm doing a project where I should work with DB2 . There is a table where two columns of type DECIMAL were made. To be more exact, the following code is created:

[DATA_VARIAZIONE] [decimal](8, 0) NOT NULL,
[ORA_VARIAZIONE] [decimal](6, 0) NOT NULL 

And what I want to do is to return a column of type TIMESTAMP based on these two values. Example values for these fields:

ORA_VARIAZIONE | DATA_VARIAZIONE|
---------------|----------------|
192416         |20180427        |

The expected result is that these values are returned as a single column of type TIMESTAMP . In other words,

DATA_ORA_VARIAZIONE|
2018-04-27-19.24.16|
    
asked by anonymous 09.05.2018 / 13:48

1 answer

0

I found this solution to the problem but I do not know if it is the best, follow the code:

select TIMESTAMP_FORMAT(substr(dt,1,4) || '-' || substr(dt,5,2) || '-' ||substr(dt,7,2) || ' '||substr(dt,9,2)||':'||substr(dt,11,2)||':'||substr(dt,13,2)||':' , 'YYYY-MM-DD HH24:MI:SS') as DATA_ORA_VARIAZIONE
from (
    select CONCAT(right( '00000000'||RTRIM(DATA_VARIAZIONE),8), right('000000'|| ORA_VARIAZIONE,6)) dt 
    FROM TABLE
    )as tabela 
    
09.05.2018 / 16:15