Question:
I have an integer column that represents
time in a 24 hour format where 0900 is 9:00 AM and 2300 is 11 PM.
How can I convert this integer data type
into a valid Oracle time datatype?
Answer:
You can use the to_date built-in function to translate the
integer into a valid time.
In the following example, to_date is used to convert an
integer into a valid time, and then embed it in a to_char
to display the integer as a time:
SQL> select to_char ( to_date ( '2300',
'HH24MI'), 'HH:MI AM') integer_time from dual;
INTEGER_TIME
--------
11:00 PM
Here is another method for converting an
integer into a AM PM time format using the
fm function:
select
to_char(
to_date('1500','HH24MI')
,'fmHH PM') integer_time
from dual;
INTEGER_TIME
-----
3 PM
These examples work when the integer time is
between 0000 (12:00 AM) and 2359 (11:59 PM).
If you have values between 0100 (1:00 AM) and
2459 (12:59) AM, you can use the CASE SQL operator to convert the
first to character representing the hour 24 to the hour 00:
--
**************************************
-- when hour is 24xx,
convert it to 00xx
-- **************************************
select
dept_time,
to_char ( to_date ( case
when substr (dept_time, 1, 2) = '24'
then
'0000'
else dept_time
end
, 'HH24MI'
),
'FMHHam'
) as
my_time
from
mytab;
|
|
 |
|
Guarantee your Success!
Oracle is the
world's most complex, robust and flexible database, considered
impossible to master without a mentor.
That's why all BC
Oracle trainers are working professionals, experts in Oracle who
share their tips and secrets. |
|
| |
|
Burleson is the American Team
Note:
This Oracle
documentation was created as a support and Oracle training reference for use by our
DBA performance tuning consulting professionals.
Feel free to ask questions on our
Oracle forum.
Verify
experience!
Anyone
considering using the services of an Oracle support expert should
independently investigate their credentials and experience, and not rely on
advertisements and self-proclaimed expertise. All legitimate Oracle experts
publish
their Oracle
qualifications.
Errata?
Oracle technology is changing and we
strive to update our BC Oracle support information. If you find an error
or have a suggestion for improving our content, we would appreciate your
feedback. Just
e-mail:
 and include the URL for the page.
Copyright ? 1996 - 2012
All rights reserved.
Oracle ?
is the registered trademark of Oracle Corporation.
|
|
