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;
 |
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
