Turn decimal into timestamp

Asked

Viewed 24 times

1

Ola, I’m doing a project where I should work with DB2. There is a table where two columns of the type were made DECIMAL.To be more exact follow the creation code:

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

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

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

The expected result is that these values are returned as a single column of type TIMESTAMP. That is to say

DATA_ORA_VARIAZIONE|
2018-04-27-19.24.16|
db2

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

Browser other questions tagged db2

You are not signed in. Login or sign up in order to post.