Database
Highlighted
Fan

4 REPLIES
Fan

Re: Difference between years

Hi All

Please help me on this.I have to find the date difference and get years.Example

 if 1 year 7 months is difference then it should be 2 years if 1 year 5 months then it should be 1 year

I tried many ways like SELECT cast(((cast('2012-07-30' as date)-cast('2011-10-30' as date))/365.25 ) as integer) as tst_ag,,but not wrking

Enthusiast

Re: Difference between years

Try with below SQL:

SELECT WholeYRS + RemainderYrs  FROM

(SELECT (DATE'2012-07-30'  - DATE'2011-10-30')MONTH  AS Months ,

CAST( Months/12 AS INT)  WholeYRS,

CASE WHEN ( CAST(Months AS INT) MOD 12) > 6  THEN 1 ELSE 0 END AS RemainderYrs)DT;

Enthusiast

Re: Difference between years

Another SQL

SELECT (CAST(Months AS INT) / 12) + (CAST(Months AS INT) mod 12) / 6 FROM

(SELECT (DATE'2012-07-30' - DATE'2011-10-30')MONTH AS Months )DT;

Junior Contributor

Re: Difference between years

What's your TD release?

SELECT ROUND(MONTHS_BETWEEN(DATE '2012-07-24', DATE '2011-10-30') / 12)