CODEWITHSUNDEEP
sqldb2
Brian
Brian

Reputation: 3

Convert string to a date in db2

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.

Upvotes: 0

Views: 1782

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

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.

Upvotes: 0

Mark Barinstein
Mark Barinstein

Reputation: 12299

If you want String -> Timestamp casting, try this:
to_timestamp('10-Feb-2019 11:20', 'dd-mon-yyyy hh24:mi')
to_timestamp is an alias for timestamp_format.

If you want just another string representation of this timestamp (like your 10-Feb-2019 11:20 -> 10-02-2019 11:20:00), try this:
to_char(to_timestamp('10-Feb-2019 11:20', 'dd-mon-yyyy hh24:mi'), 'DD-MM-YYYY HH24:MI:SS')
to_char is an alias for varchar_format.

Upvotes: 1

Related Questions