I need difference of Given Date and Today's date but in User Defined format i.e. Years=XX,Months=YY,Days=ZZ. (XX-no of years, YY-no of month, ZZ-no of days) User will add a date for e.g. "08/20/2014" i need to write a query which can give me output in this format - Years=1,Months=2,Days=19 whether ADS having readymade function? i tried with few queries but its not working as per expected...

my query is

SELECT "DATE",NUMDAYS, 'Years ='+ CAST(IIF(NUMDAYS>365,TIMESTAMPDIFF(SQL_TSI_YEAR,"DATE",convert(CURDATE(),SQL_DATE)),0) as SQL_CHAR) + 'Months ='+ CAST(IIF(NUMDAYS>30,IIF((NUMDAYS/30)>12,FLOOR(NUMDAYS/30)-12,FLOOR(NUMDAYS/30)),0) as SQL_CHAR) + 'Days ='+ CAST(IIF(NUMDAYS>30,MOD(CAST(NUMDAYS As SQL_INTEGER),30),NUMDAYS) as SQL_CHAR) AS Duration From XXX where XXX.ExamNo = 12345

there is no ready-made function because this is something wery specific. To get the distance between two date values in days, just substract one from the other: date1-date2. If you define a month always to be 30 days and a year always to be 365 days, then you can just calculate with that result.