Hi,
I'm trying to calculate length of service in format years, months and days as difference between Hire date and current date.
I have noticed that there is problem with day count for employees which are hired on day number higher than day number for current date as it is negative.
What I have done wrong?
CAST((TIMESTAMPDIFF(sql_tsi_month, "Person"."Hire Date", current_date)/12) as CHAR(2))||' year(s), ' ||
CAST(TIMESTAMPDIFF(sql_tsi_month, "Person"."Hire Date", current_date)-((TIMESTAMPDIFF(sql_tsi_month, "Person"."Hire Date", current_date)/12)*12) AS CHAR(2))||' month(s), '||
CAST(current_date - timestampadd(sql_tsi_month, TIMESTAMPDIFF(sql_tsi_month, "Person"."Hire Date", current_date), "Person"."Hire Date") AS CHAR(3))||' day(s)'
I assume that in part responsible for day calculation somehow should be used part responsible for month calculation.
and for example for line one it should be: 65 yrs, 6 months, 23 days instead of 65 yrs, 7 months, -7 days.
I have tried to translate this SQL for OBIEE formula, but something I done wrong.