Time from numeric

[lubka]

Hello!
Please does anybody know how to get TIME datatype from a value, which is stored as NUMERIC in DB2 table ?

I have got a table with a field ABC type NUMERIC(6) storing values like this: 122519, 93343, 184701, ... and I need to get values in DATE format (12:25:19, 09:33:43, 18:47:01, ...), so what to store into a SQL command?

SELECT WhatFunction(ABC) as NEWTIMEVAL

Thanks a lot !!!

lubka

 

[Mercury2]

Using TIME function :

SELECT TIME(SUBSTR(CHAR(ABC), 1, 2) Concat ':' Concat
                  SUBSTR(CHAR(ABC), 3, 2) Concat ':' Concat
                  SUBSTR(CHAR(ABC), 5, 2)) as NEWTIMEVAL

Or using CAST function :

SELECT CAST(SUBSTR(CHAR(ABC), 1, 2) Concat ':' Concat
                  SUBSTR(CHAR(ABC), 3, 2) Concat ':' Concat
                  SUBSTR(CHAR(ABC), 5, 2) as TIME) as NEWTIMEVAL

Make your pick.

 

[lubka]

Thanks a lot !
But can I use function SUBSTR for NUMERIC field type ??

Thanks, lubka

 

[Mercury2]

can I use function SUBSTR for NUMERIC field type ?

You must first convert numeric fields to character as shown on my previous post, e.g. SUBSTR(CHAR(ABC), 1, 2)

 

[lubka]

I'm very sorry for my inattention!
Thanks a lot!
lubka

 

[lubka]

Maybe one more idea - would it be possible to define TIME format data field from NUMERIC data field (in a PHYSICAL FILE) in a LOGICAL FILE? Any CASTing ?

In PF I have ABC NUMERIC(6), i would like do define this field in LF like TIME

Thanks, lubka

 

[Mercury2]

AFAIK, TIME like DATE are special fields and you can NOT convert numeric fields or alphameric fields to TIME fields or DATE fields using a logical file. Use SQL instead.

 

[lubka]

That's how I do it now.
One more thanks !
lubka