CODEWITHSUNDEEP
sqloracle-databasetypes
santhosha
santhosha

Reputation: 629

Convert VARCHAR to Time in oracle

I am using ORACLE and I want to convert VARCHAR type into Time using following SQL Query

SELECT CUSTOMER_SERVICE_QUOTE_MEAS.ITEM_CREATE_TM,
TO_char(
CUSTOMER_SERVICE_QUOTE_MEAS.ITEM_CREATE_TM,
'hh24:mi:ss')
AS TIME_CHANGE
FROM FDC.CUSTOMER_SERVICE_QUOTE_MEAS CUSTOMER_SERVICE_QUOTE_MEAS

This results in 6/1/2013 11:32:02 AM but I don't want the date part here.
I need only time portion 11:32:02, How it can be done ?

The field CUSTOMER_SERVICE_QUOTE_MEAS.ITEM_CREATE_TM has the data which look like this 113202. Any help will be appreciated, Thanks.

Upvotes: 2

Views: 48687

Answers (2)

ExcessOperatorHeadspace
ExcessOperatorHeadspace

Reputation: 319

Is the datatype of the item_create_tm column a NUMBER? The to_char is expecting a date column. To replicate the error: 

SELECT to_char(113202, 'hh24:mi:ss') FROM dual;

It generates this error.

ORA-01481: invalid number format model
01481. 00000 -  "invalid number format model"
*Cause:    The user is attempting to either convert a number to a string
           via TO_CHAR or a string to a number via TO_NUMBER and has
           supplied an invalid number format model parameter.
*Action:   Consult your manual.

You could either transform it to a date first and then to a formatted char. 

SELECT to_char(to_date(113202, 'hh24miss'), 'hh24:mi:ss') FROM dual;

Output

11:32:02

Or substring the number to a char with : separators.

SELECT substr(113202, 1, 2) || ':' || substr(113202, 3, 2) || ':' || substr(113202, 5, 2) FROM dual;

Output

11:32:02

Upvotes: 8

Randy
Randy

Reputation: 16677

you can wrap dates with to_char and a format something like this:

select to_char( mydate, 'hh24:mi:ss' ) as mytime from mytable;

Upvotes: 0

Related Questions