Archives of the TeradataForum

Message Posted: Wed, 23 Jul 2008 @ 14:57:48 GMT


	<Prev	Next>		<<First	<Prev	Next>	Last>>

Subj:		Re: How to Achieve the decimal part

From:		Victor Sokovin

The Months_Between function calculates the fraction month based on a 31 day month.

Wouldn't it be more accurate to calculate the difference by dividing the date difference by 30.4375, the result of dividing 365.25 by 12?

Oracle are not into accuracy with this function. They try to imitate some intuitive ideas about time intervals measured in "months":

asktom.oracle.com/pls/asktom

     SELECT MONTHS_BETWEEN(TO_DATE('30122005','DDMMYYYY'),
     TO_DATE('30112005','DDMMYYYY')) Months FROM DUAL

     1

     SELECT MONTHS_BETWEEN(TO_DATE('31122005','DDMMYYYY'),
     TO_DATE('30112005','DDMMYYYY')) Months FROM DUAL

     1

     SELECT MONTHS_BETWEEN(TO_DATE('01012006','DDMMYYYY'),
     TO_DATE('30112005','DDMMYYYY')) Months FROM DUAL

     1.06451612903226

The function won't meet everybody's needs but it's just there for those who are ready to accept its "logic" without doing more complicated date calculations.

Nice task to convert this all to Teradata. Hopefully, well paid for.

Victor


	<Prev	Next>		<<First	<Prev	Next>	Last>>

Attachments

Library

Quick Reference