1 Reply Latest reply: Jan 8, 2014 3:39 AM by _Phil RSS

Convert Time

Moazam Shareef Explorer
Currently Being Moderated

hi guyz,

 

I imported some data from Microsoft Access into Oracle where some time field is display as 210539  as time i want to convert this time in property format IE (21:05:39 HH24MISS) but when i'm using the below query it showing error anything wrong with my query? i

 

SQL> SELECT TO_CHAR(101550,'HH24:MI:SS')FROM DUAL;
SELECT TO_CHAR(101550,'HH24:MI:SS')FROM DUAL

                      *

ERROR at line 1:

ORA-01481: invalid number format model

 

i'm using oracle 10g.

  • 1. Re: Convert Time
    _Phil Journeyer
    Currently Being Moderated

    You're trying to convert a number to a string while using a datetime mask - this isn't allowed.  To use 'HH:MI:SS', the value to be converted must be a datetime type.

     

    e.g. this will work:

     

    SELECT TO_CHAR(to_date('101550', 'HH24MISS'),'HH24:MI:SS')FROM DUAL

     

    ...because now we've told Oracle that 101550 is actually a datetime in the format 'HH24MISS'.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points