CODEWITHSUNDEEP
oracle-database
Waterbottle
Waterbottle

Reputation: 17

How to convert time to number in Oracle SQL

I have a column with time datatype like for example:

01:00
24:00
03:00

I need to convert this time to number so it will be like

0100
2400
0300

Upvotes: 1

Views: 4300

Answers (2)

Littlefoot
Littlefoot

Reputation: 142713

Well, looking at what you posted, if values that column contains look exactly like that, then it is one of CHAR family datatypes because there's no TIME datatype in Oracle, as pmdba already told you. It would certainly help if you explained it properly, instead of letting us guess.

Anyway: if that's so and if values really are stored that way:

SQL> create table test as
  2  select '01:00' col from dual union all
  3  select '24:00'     from dual union all
  4  select '03:00'     from dual;

Table created.

SQL> desc test
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------------------
 COL                                                CHAR(5)

then you'd just have to remove the colon:

SQL> select col, replace(col, ':', '') result
  2  from test;

COL   RESULT
----- ----------
01:00 0100
24:00 2400
03:00 0300

SQL>

As I said: explain it properly.

Upvotes: 1

pmdba
pmdba

Reputation: 7033

First, Oracle does not have a "time" datatype. If you are working with a date or a timestamp datatype, you can use the TO_CHAR function to format the time portion of the data in whatever style you want. The following would return the style you describe, using HH24 to convert hours to the hour format and MI to return the minutes:

TO_CHAR(date_column,'HH24MI')

For example:

select to_char(sysdate,'HH24MI') from dual;

TO_CHAR(SYSDATE,'HH24MI') 
------------------------- 
0331                      

Elapsed: 00:00:00.007
1 rows selected.


select to_char(systimestamp,'HH24MI') from dual;

TO_CHAR(SYSTIMESTAMP,'HH24MI') 
------------------------------ 
0331                           

Elapsed: 00:00:00.006
1 rows selected.

Upvotes: 2

Related Questions