Change Number to Time format [message #349475] |
Mon, 22 September 2008 00:46  |
Notorious96
Messages: 7 Registered: September 2008
|
Junior Member |
|
|
the existing database has a time in a wierd format where the date was imported without the 0s to show HHMMSS format.Hence for a call made at 00:12:41 .. the data is stored as 1241
I was told to use lpad(string, total number of digits, 'char') which adds 0s to the left to make it a 6 digit number so as to have it in HHMMSS format where 1241 will become 001241
My question is .. how to convert a number of 6 digits that is in HHMISS format to get an output of HH:MI:SS format.
I tried to_char(TIMEOFCALL,'HH:MI:SS') which results in an error.
any suggestions?
[Updated on: Mon, 22 September 2008 00:47] Report message to a moderator
|
|
|
|
Re: Change Number to Time format [message #349519 is a reply to message #349475] |
Mon, 22 September 2008 02:28   |
 |
Alessandro Rossi
Messages: 166 Registered: September 2008 Location: Rome
|
Senior Member |
|
|
Did you mean something like this
Processing ...
select :inp as original,
to_char(:inp,'000000') as string,
to_date(to_char(:inp,'000000'),'HH24MISS') as time
from dual
Query finished, retrieving results...
ORIGINAL STRING TIME
-------------------------------------- ---------- -------------------
1241 001241 1/09/2008 0:12:41
1 row(s) retrieved
Bye Alessandro
[Updated on: Mon, 22 September 2008 02:44] Report message to a moderator
|
|
|
Re: Change Number to Time format [message #349525 is a reply to message #349519] |
Mon, 22 September 2008 02:36   |
 |
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Alessandro, please at least let OP answer the questions before answering.
And remember, don't put solution only hint or clue as requested in OraFAQ Forum Guide, "Responding to Posts" section:
Quote: | When responding to questions, if it is obviously a student with a homework assignment or someone else just learning, especially in the homework and newbies forums, it is usual to provide hints or clues, perhaps links to relevant portions of the documentation, or a similar example, to point them in the right direction so that they will research and experiment on their own and learn, and not provide complete solutions to problems. In cases where someone has a real-life complex work problem, or at least it may seem complex to them, it may be best to provide a complete demo and explanation.
|
Regards
Michel
|
|
|
|
|
|
|
|
|
|
Re: Change Number to Time format [message #349561 is a reply to message #349557] |
Mon, 22 September 2008 04:08   |
Notorious96
Messages: 7 Registered: September 2008
|
Junior Member |
|
|
I forgot to mention something .. the coloumn only contains the time .. in 1241 format .. it doesn't contain the date.
Meaning the data is present as follows
TIMEOFCALL
1241
4521
3224
etc. .. not in the 200809201241 format where date is also present.
Only the time is present
thanks for all the help in advance!
|
|
|
Re: Change Number to Time format [message #349562 is a reply to message #349552] |
Mon, 22 September 2008 04:10   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
here's a couple of ways fo doing it:select regexp_replace('001241','([0-9]{2})([0-9]{2})([0-9]{2})','\1:\2:\3') from dual;
select replace(to_char(1241,'00,00,00'),',',':') from dual;
|
|
|
|