DB2 - Argument 02 of function TRANSLATE not valid

Question

I am having some issues converting a string (yyyymmddhhiiss) to a date using TRANSLATE.

If I use a string directly then it works perfectly fine, but when i use a field of exactly same datatype, varchar(14), then it throws the error from the title.

Here is a basic example of what i am trying to do:

WITH test_table AS (
    SELECT '20160101123059' AS d FROM SYSIBM.SYSDUMMY1
)
SELECT d
       , translate('ABCD-EF-GH IJ:KL:MN', d, 'ABCDEFGHIJKLMN')
       , translate('ABCD-EF-GH IJ:KL:MN', '20160101123059','ABCDEFGHIJKLMN')
  FROM test_table

Can one of you explain why this is not working? Thanks.

Answer 1

From the DB2 for i manual...

to-string
A string that specifies the characters to which certain characters in expression are to be converted. This string is sometimes called the output translation table. The string must be any built-in numeric or string constant.

So it won't work the way you're trying to use it.

Argument 2 must be a constant value.

Assuming a supported release of the IBM i, you should be able to use the timestamp() function to convert the 14-character string directly to timestamp.

select timestamp('20160101123059')
from sysibm.sysdummy1             

Answer 2

You cas use : TIMESTAMP_FORMAT('20160101123059' , 'YYYYMMDDHH24MISS')

If you want a char to result: cast(TIMESTAMP_FORMAT('20160101123059' , 'YYYYMMDDHH24MISS') as varchar(50))