All Forums Database
reshmietl11 2 posts Joined 06/15
22 Jun 2015
Difference between years
reshmietl11 2 posts Joined 06/15
22 Jun 2015

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

anil.doobagunta 11 posts Joined 10/11
23 Jun 2015

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;

anil.doobagunta 11 posts Joined 10/11
23 Jun 2015

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;

dnoeth 4628 posts Joined 11/04
23 Jun 2015

What's your TD release?
SELECT ROUND(MONTHS_BETWEEN(DATE '2012-07-24', DATE '2011-10-30') / 12)

Dieter

You must sign in to leave a comment.