OraFAQ Forum: SQL & PL/SQL » How to format number to a time

Home » SQL & PL/SQL » SQL & PL/SQL » How to format number to a time

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to format number to a time [message #237626]

Tue, 15 May 2007 04:36

manoj_vilayil
Messages: 9
Registered: May 2007
Location: India

Junior Member

Sir,
Suppose in the back end there is a field called called start time in number format.How can i convert it to HH:MI format

Eg: Start Time = 8.3
I want the output in 08:30

Start Time = 1.05
I want output in 01:05

Report message to a moderator

Re: How to format number to a time [message #237628 is a reply to message #237626]

Tue, 15 May 2007 04:40

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Do you mean 8.3 means 8 hours 30 minutes or 8.3 hours (8 hours 24 minutes) or...?

Regards
Michel

[Updated on: Tue, 15 May 2007 04:41]

Report message to a moderator

Re: How to format number to a time [message #237629 is a reply to message #237628]

Tue, 15 May 2007 04:44

manoj_vilayil
Messages: 9
Registered: May 2007
Location: India

Junior Member

Ya i mean 8.3 means 8 hours 30 minutes
and 8.03 means 8 hours 03 minutes

Report message to a moderator

Re: How to format number to a time [message #237633 is a reply to message #237629]

Tue, 15 May 2007 04:59

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

SQL> with data as (select 8.3 t from dual),
  2       split as (select trunc(t) hour, mod(t*100,100) min from data)
  3  select to_char(hour,'fm00')||':'||to_char(min,'fm00') time
  4  from split
  5  /
TIME
-------
08:30

Regards
Michel

Report message to a moderator

Re: How to format number to a time [message #237638 is a reply to message #237633]

Tue, 15 May 2007 05:15

Frank
Messages: 7901
Registered: March 2000

Senior Member

SQL> with data as (select 8.3 t from dual)
  2  select replace(to_char(data.t, 'fm00.00'), '.', ':') from data;

REPLAC
------
08:30

This has nothing to do with times or dates or whatever. You just want to fill out with zeros and use a colon instead of a dot.

[Edit: added fm to format-mask]

[Updated on: Tue, 15 May 2007 05:16]

Report message to a moderator