• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3490
  • Last Modified:

Convert Number into HHMMSS Format

Hi,

I need a requirement to convert a number field into HHMMSS format. The field is a number field in the oracle database like Order_Time. If anybody can help me out getting the above format, I would appreciate.

Thanks.
0
asimahmed073100
Asked:
asimahmed073100
  • 4
  • 3
  • 2
  • +5
1 Solution
 
actonwangCommented:
What does this number field look like? How does it stand for a time?

Did you try to_date() ?
0
 
radja7Commented:
SELECT to_char(trunc(sysdate)+your_table.Order_Time,'hh24miss') FROM vyour_table;
0
 
qball2525Commented:
have you try this
TO_DATE('165200','HH24MISS')

SELECT * FROM YOUR_TABLE WHERE TIME=TO_DATE('165200','HH24MISS') ;

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
asimahmed073100Author Commented:
Hi,

I am getting the following error

ERROR at line 1:
ORA-01850: hour must be between 0 and 23

The time field has value somewhat 34247.
0
 
asimahmed073100Author Commented:
One thing I forgot to mention is the value in Oracle is a numeric serial representation of time.
0
 
fmonroyCommented:
I think you are using a TIMESTAMP datatype...

try this:

select to_char(COLUMN_NAME,'HH24MMSS') from TABLE_NAME;
in 24 hour format

or

select to_char(COLUMN_NAME,'HH12MMSSAM') from TABLE_NAME;
in 12 hour format

hope it helps.

FM.
0
 
actonwangCommented:
anyway, what is the datetype of your column?
0
 
asimahmed073100Author Commented:
The datatype of the column is number.
0
 
cjardCommented:
ok, so its a number.. how does it relate to a time?


i.e. if your number column contians 78564.. exactly how is this a time? how would you convert 78654 into a time and what time would it be?
0
 
cjardCommented:
or another example, you mention your column has a vlaue of 34247

what time does it mean?
3:42:47?
34247 seconds since midnight on jan 1, 1970


you really need to give us more information..
0
 
asimahmed073100Author Commented:
I tried this approach and it seems to be working fine. This approach I am doing using VBScript but wondering if we can use this in Oracle...

1) Divide by the number of hours, minutes, seconds in a day (24 * 60 * 60) = 86400
Result_Val = Time_Val / 86400

2) Use the standard VB Hour(), Minute(), Second() functions to identfy the time units, pad them with zeroes, and concatenate into a single value

New_Val = Right("00" & Trim(Hour(Result_Val)), 2) & Right("00" & Minute(Result_Val), 2) & Right("00" & Second(Result_Val), 2)
0
 
radja7Commented:
try this:
SELECT to_char(trunc(sysdate)+Time_Val/86400,'hh24miss') FROM table;
0
 
fmonroyCommented:
asimahmed: lets see:

Result_Val = Time_Val / 86400

lets say: 14:20:35
so
Time_Val = 14*60*60 + 20*60 + 35
Time_val = 51635

I think you are storing this value in a column, then u need to convert it back to time:
as radja7 said:

select to_char(trunc(sysdate)+51635/86400,'HH24MISS') from dual;

the result is:
142035
0
 
MohanKNairCommented:
The Date format converter SSSSS gives Seconds past midnight (0-86399).

select to_char(to_date(to_char(29791), 'sssss'), 'HHMiSS') from dual;

0
 
fmonroyCommented:
It can be helpful for others, please dont erase it.
0
 
Computer101Commented:
PAQed with points refunded (125)

Computer101
EE Admin
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 4
  • 3
  • 2
  • +5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now