How to convert minutes to H:M:S format

Database
Kks
Enthusiast

How to convert minutes to H:M:S format

Hi all,

is there any inbuilt function to convert minutes to H:M:S format in SQL

Thanks
8 REPLIES
Teradata Employee

Re: How to convert minutes to H:M:S format

Hello,

Can you please provide an example what exactly you want to do?

Regards,

Adeel
Kks
Enthusiast

Re: How to convert minutes to H:M:S format

Hi,
What I am trying to achieve is like 312 minutes
if converted will be like 05:12:00 (HMS) format
Enthusiast

Re: How to convert minutes to H:M:S format

select cast(interval '312' minute AS interval hour to minute);

*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.

312
------
5:12
Kks
Enthusiast

Re: How to convert minutes to H:M:S format

Thanks for reply , This interval '312' canbe a column from table

e.g.

select cast(interval A minute AS interval hour to second);

but I got syntax error .
Kks
Enthusiast

Re: How to convert minutes to H:M:S format

sorry e.g. is

select cast(interval A minute AS interval hour to second) FROM TAB1;

Enthusiast

Re: How to convert minutes to H:M:S format

The form "INTERVAL 'string' MINUTE" is an interval literal. If you want to convert a character or integer type value, it seems that you need 2 casts.

create table t1 (a int, b char(3));

*** Table has been created.

insert into t1 values (312, '312');

*** Insert completed. One row added.

select cast( cast(b as interval minute(3)) as interval hour to second(0) )
from t1;

*** Query completed. One row found. One column returned.

b
---------
5:12:00
Enthusiast

Re: How to convert minutes to H:M:S format

hello,
I was trying to do this for seconds and i am running into issues.
How can we convert a field that has integer (seconds) into time format hh:mm:ss?

i was getting some interval field overflow errors, and Number of leading digits out of range. error.

basically this is what we are trying to do.
we want to create a table that has all the possible times
starting from 00:00:00
thru 23:59:59

appreciate your help.
Enthusiast

Re: How to convert minutes to H:M:S format

got it!

select '123' * CAST( INTERVAL '1' SECOND AS INTERVAL hour TO second(0));