Home » SQL & PL/SQL » SQL & PL/SQL » How to format number to a time
icon2.gif  How to format number to a time [message #237626] Tue, 15 May 2007 04:36 Go to next message
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
Re: How to format number to a time [message #237628 is a reply to message #237626] Tue, 15 May 2007 04:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
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
Re: How to format number to a time [message #237633 is a reply to message #237629] Tue, 15 May 2007 04:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: How to format number to a time [message #237638 is a reply to message #237633] Tue, 15 May 2007 05:15 Go to previous messageGo to next message
Frank
Messages: 7880
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

Re: How to format number to a time [message #237644 is a reply to message #237638] Tue, 15 May 2007 05:43 Go to previous messageGo to next message
manoj_vilayil
Messages: 9
Registered: May 2007
Location: India
Junior Member
Thank u sir very much for your valuable guidance
Re: How to format number to a time [message #237770 is a reply to message #237626] Tue, 15 May 2007 12:29 Go to previous message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
removed by entry, I just noticed that frank beat me to it.

[Updated on: Tue, 15 May 2007 12:29]

Report message to a moderator

Previous Topic: Trigger
Next Topic: Table Partition
Goto Forum:
  


Current Time: Sat Dec 10 22:15:57 CST 2016

Total time taken to generate the page: 0.10462 seconds