select * from v$version;
Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
"CORE 11.2.0.1.0 Production"
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
Kindly check this out. It seems as if the
numtoyminterval function rounds values lower than 1 month differently then higher values.
with testdata as (select 0.1 as wert from dual
union all
select 1.1 as wert from dual
union all
select 1.0 as wert from dual
union all
select 0.9 as wert from dual
union all
select 0.001 as wert from dual
union all
select 1.9 as wert from dual
)
select wert
, numtoyminterval(wert,'MONTH') intervall
, extract(year from numtoyminterval(wert,'MONTH')) ||'J '|| extract(month from numtoyminterval(wert,'MONTH')) || 'M' as prettyprint
from testdata
order by wert;
WERT INTERVALL PRETTYPRINT
------------------------------------------------------
0,001 0-0 0J 0M
0,1 0-1 0J 1M "this should be 0J 0M!"
0,9 0-1 0J 1M
1 0-1 0J 1M
1,1 0-1 0J 1M
1,9 0-2 0J 2M
Note that result values printed as 1,9 are 1.9 cause of my german nls settings.
Can anybody reproduce it or is this a known bug?
Edited by: Sven W. on Jun 5, 2013 10:50 AM