Re: Return minutes as date datatype ++ implicit datatype conversion

"Craig & Co." <crb_at_amsa.gov.au> wrote in message news:40ff4f51$0$51369$c30e37c6_at_lon-reader.news.telstra.net...
| > 1* select To_Char(sysdate,'HH24MI') from dual
| > SQL> /
| >
| > TO_C
| > ----
| > 1725
| >
| > Seems numeric enough to me?
|
| That's the way I'd do it.
|
| Craig.
|
|

beware of implicit datatype conversions -- to_char returns a VARCHAR2 datatype, and any code that checks the datatype may not function as assumed

in this example SQL*Plus is asked to compute an average on the hiretime column and is unable to do so with the TO_CHAR expression -- properly converting it to a number yields the correct results:

SQL> break on report
SQL> compute avg of hiretime on report

SQL> select ename, hiredate, to_char(hiredate,'hh24mi') as hiretime from emp;

ENAME HIREDATE HIRE

---------- --------- ----
SMITH      17-DEC-80 0001
ALLEN      20-FEB-01 0002
WARD       22-FEB-81 0003
JONES      02-APR-81 0004
MARTIN     28-SEP-01 0005
CLARK      09-JUN-81 0006
SCOTT      09-DEC-82 0007
KING       17-NOV-81 0008
TURNER     08-SEP-81 0009
Adams      12-JAN-83 0010
JAMES      03-DEC-81 0011
FORD       03-DEC-81 0012
MILLER     23-JAN-82 0013
                     ----

13 rows selected.

SQL> select ename, hiredate, to_number(to_char(hiredate,'hh24mi')) as hiretime from emp;

ENAME HIREDATE HIRETIME

---------- --------- ----------
SMITH      17-DEC-80          1
ALLEN      20-FEB-01          2
WARD       22-FEB-81          3
JONES      02-APR-81          4
MARTIN     28-SEP-01          5
CLARK      09-JUN-81          6
SCOTT      09-DEC-82          7
KING       17-NOV-81          8
TURNER     08-SEP-81          9
Adams      12-JAN-83         10
JAMES      03-DEC-81         11
FORD       03-DEC-81         12
MILLER     23-JAN-82         13
                     ----------
avg                           7

13 rows selected.

++ mcs Received on Thu Jul 22 2004 - 07:06:56 CDT