CODEWITHSUNDEEP
oracle11g
user6404269
user6404269

Reputation: 33

Oracle NUMTOYMINTERVAL

Can someone explain the logic of NUMTOYMINTERVAL . I went through various websites, but could not get it.

Can you please explain on below specific examples

 select (NUMTOYMINTERVAL(30,'month')) from dual;   
 select (NUMTOYMINTERVAL(30,'YEAR')) from dual; 
 PARTITION BY RANGE (RECORDDATE) INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))

Thanks in advance

Upvotes: 2

Views: 4101

Answers (1)

Ditto
Ditto

Reputation: 3344

This site here gives a good explanation:

http://www.orafaq.com/wiki/Interval

INTERVAL datatype is a data type used to store a period of time. There are 2 kinds of interval: INTERVAL YEAR TO MONTH, to store a difference in years and months, and INTERVAL DAY TO SECOND, to store a difference in days, hours, minutes and seconds.

it provides a way of indicating a LENGTH of time .. either YEAR/MONTH (NUMTOYMINTERVAL) or days/hours/min/secods: (TO_DSINTERVAL)

So in your first example:

  SQL > select (NUMTOYMINTERVAL(30,'month')) from dual;
  -------------- More --------------

  (NUMTOYMINTERVAL(30,'MONTH'))
  ---------------------------------------------------------------------------
  +000000002-06

  1 row selected.

It returns an INTERVAL representing the length of time for "30 months" .. the result is showing "2 years and 6 months" ..

On a side note, this post by Tom Kyte is interesting, he recommends avoid using NUMTOYMINTERVAL due to behaviour near end of months

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:68920891856765

I do not recommend using the NUMTOYMINTERVAL function. The reason has to do with how the functions behave at the months end.

Upvotes: 2

Related Questions