convert integer to 24 hours :DB2-sql-CodePudding

I have a table with integer values. I need to convert the integer value into 24 hour format,how i can do it i tried to use FOM/60 but it gives fel result

From          ------------ what i want to se
570                          9:30               
600                          10:00
700                          11:40
1020                         17:00

CodePudding user response：

Use this snipets:

CREATE TABLE numbers (
  Num INTEGER 
);
INSERT INTO numbers VALUES (570);
INSERT INTO numbers VALUES (600);
INSERT INTO numbers VALUES (700);
INSERT INTO numbers VALUES (1020);

SELECT cast(Num/60 as nvarchar) ':' right ('00' ltrim(str( Num` )),2 ) FROM numbers

Output:

CodePudding user response：

Using time type you can just add minutes to midnight

with table1 (val) as (values 570, 600, 700, 1020, 1234)
select time('00:00')   val minutes from table1