CODEWITHSUNDEEP
oracle-databaseselecttimevarchar
Prabakar Gutz
Prabakar Gutz

Reputation: 37

Conver varchar value as Time in oracle

I have numerical string in varchar column need to return those values in time format

Time
----
1560
1350
1561

output should be like below

time
----
00:15:60
00:13:50
00:15:61

im not good in database..can any one help me to return like this...

Upvotes: 0

Views: 67

Answers (2)

Pravin Satav
Pravin Satav

Reputation: 702

In Oracle, DATE columns (and related types, like TIMESTAMP) always include year, month, day, hour, minute and second. You can't have a DATE without having all of them. You can, however, specify a DATE without specifying all of them: the parts that you leave out will get default values. If you don't specify a month, it defaults to the current month. If you don't give a day, it defaults to the 1st of the month.

If that doesn't suit your needs, use some data type other than DATE.

Upvotes: 0

Emanuele
Emanuele

Reputation: 479

00:15:60 is not a valid hour format... what do you mean? 15minutes and 60seconds? why not use 16minutes instead?

anyway, after you fix the value of seconds passed in, use the query below

select
     to_char(
       to_date('001500','HH24MISS')
     ,'HH24:MI:SS')
from dual;

Upvotes: 1

Related Questions