Number to Time Conversion

[garreth.kelly]

is it possible in sql to convert a 6 digit integer in to a time field
with out converting to character and then substringing
e.g
161201 to 16:12:01

[Tonkuma]

SELECT TIME('00:00:00') + CAST(int6 AS DEC(6,0)) "Time"
FROM (VALUES 161201) Q(int6);
------------------------------------------------------------------------------

Time
--------
16:12:01

1 record(s) selected.

OR

SELECT TIME('00:00:00')
+ (int6/10000) Hours
+ MOD(int6/100, 100) Minutes
+ MOD(int6, 100) Seconds AS "TIME"
FROM (VALUES 161201) Q(int6);
------------------------------------------------------------------------------

TIME
--------
16:12:01

1 record(s) selected.

[Serge Rielau]

Tonkuma wrote:

SELECT TIME('00:00:00') + CAST(int6 AS DEC(6,0)) "Time"
FROM (VALUES 161201) Q(int6);
------------------------------------------------------------------------------

Time
--------
16:12:01

1 record(s) selected.

OR

SELECT TIME('00:00:00')
+ (int6/10000) Hours
+ MOD(int6/100, 100) Minutes
+ MOD(int6, 100) Seconds AS "TIME"
FROM (VALUES 161201) Q(int6);
------------------------------------------------------------------------------

TIME
--------
16:12:01

1 record(s) selected.

... or encapsulated:
CREATE FUNCTION TIME(int6 INT)
RETURNS TIME
CONTAINS SQL NO EXTERNAL ACTION DETERMINISTIC
RETURN
TIME('00:00:00')
+ (int6/10000) Hours
+ MOD(int6/100, 100) Minutes
+ MOD(int6, 100) Seconds

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[garrethkelly]

cheers !