Re: Sunday Moment ...

On 25-May-2016 08:08 -0500, Charles Wilt wrote:

Side note, I just saw this technique for the first time a few days
ago.

However, in that case, the person was trying to use it in SQL...

select translate( 'ABCD-EF-GH IJ:KL:MN'
, '20160101123059'
, 'ABCDEFGHIJKLMN' )
from sysibm.sysdummy1

Works quite nicely. However, the user was trying to do

select translate( 'ABCD-EF-GH IJ:KL:MN'
, mycolumn
, 'ABCDEFGHIJKLMN')
from mytable

And that was failing as SQL's translate() function requires a string
constant for parameter #2. <<SNIP>>

FWiW, though you likely already know, but I figured other readers might be interested to know:

For that scenario, given the apparent string values for MyColumn are each a valid string-representation of a TIMESTAMP [of the "14–character form 'yyyymmddhhmmss'"], such values would be easily-enough converted directly into the desired character string format, using the TO_CHAR [aka VARCHAR_FORMAT] scalar. Unlike a[n attempted] use of TRANSLATE, the intended effects are likely quite conspicuous to any reader; both the format-string and the function-name likely assist in revealing the intention:

Explicitly specified TIMESTAMP of the 14-char form to best elucidate to a reader, what is going on:

values( to_char( timestamp('20160101123059')
, 'YYYY-MM-DD HH24:MI:SS' )
)

Implicitly cast from char-14 to TIMESTAMP is less conspicuous; i.e. the reader likely would wonder "a char-string into formatted char?"

values( to_char( '20160101123059'
, 'YYYY-MM-DD HH24:MI:SS' )
)

So with a column [perhaps named such that TIMESTAMP is a bit more obvious; or again, the TIMESTAMP casting scalar can be used like in the first of the above VALUES], the conversion to char[acter] into that format is:

select to_char(mycolumn,'YYYY-MM-DD HH24:MI:SS')
from mytable