CODEWITHSUNDEEP
sqldb2
Mohammad Tabish
Mohammad Tabish

Reputation: 11

Convert DB2 SQL Decimal to time

Convert DB2 SQL Decimal to time I need to convert Decimal to time. I have a decimal time field that contains data like this :

123490 --12:34:90
4506 --00:45:06
171205 --17:12:05

etc

I would like to convert into time format then i calculate min between two times columns

Is there anyway to do this with SQL commands or DB2 logic in a select statement?

Upvotes: 1

Views: 6894

Answers (2)

Peter
Peter

Reputation: 473

You can use TO_DATE, which is short version for TIMESTAMP_FORMAT, but you first need to convert decimal to string.

select time(to_date(digits(cast(143513 as dec(6,0))),'HH24MISS')) from sysibm.sysdummy1
  • cast is there just to simulate 6 digit decimal
  • digits converts decimal to char, padding it with zeros
  • to_date converts string to timestamp, based on the format string. TIMESTAMP_FORMAT
  • time returns the time portion of a timestamp

This is based on iSeries version of DB2 but should work for LUW as well.

Upvotes: 0

MichaelTiefenbacher
MichaelTiefenbacher

Reputation: 4005

To convert it to a valid time format you have to do some string manipulations like 

cast( substr( right( '00' || '123456', 6) ,1,2) || ':' || substr( right( '00' || '123456', 6) ,3,2) || ':' || substr( right( '00' || '123456', 6) ,5,2) as time)

where 123456 is your decimal. This would work for your 4506 example as well. You could of cause also use a case statement if you want to avaoid adding the "00" each time.

For calculating the difference in minues there might be other calc options. You can check out the minutes_between function provided by Db2 11.1

Upvotes: 3

Related Questions