Ok I was able to get it fixed.
I want to convert the string “10-Feb-2019 11:20” to “DD-mm-yyyy hh24:mi:ss”.
I tried to date but it wasn’t working. I saw on other threads that the translate function might be the way to go. However I am somewhat new to SQL and none of the explanation of how to use translate in this manner where in a way I could understand.
I am using windows and a pretty recent version of db2
The original is
select
case
when (length(column) = 50 then
to_date(substr(column, 1, 17),’dd-mm-yyyy hh24:mi’))
else
date
end
from table
So it is finding the date within a larger string.
the code now is
Select
case
when (length(column) = 50 then
to_timestamp(substr(column, 1, 17),’dd-mm-yyyy hh24:mi’))
else
date
end
From table
Note: I just tried to_timestamp(’10-Feb-2019 11:20′, ‘dd-mm-yyyy hh24) and I got “10-Feb-2019 11:20” cannot be interpreted using format string “dd-mm-yyyy hh24:mi” for the TIMESTAMP_FORMAT function.
Advertisement
Answer
In DB2, date does not have a time component.
So, try to_timestamp():
select to_timestamp('10-Feb-2019 11:20', 'DD-Mon-yyyy hh24:mi')
Note that seconds are not in the string value, so they should not be in the format specification. However, the final value will have seconds — all set to zero.