sanu j sanu j - 2 months ago 17
SQL Question

SQL query to find out experience of employee in the format 'yy years mm months dd days'

I want a query to find out experience of employee in the format 'yy years mm months dd days'.

SELECT EMPID, EMPNAME, DEPARTMENT, DESIGNATION, DATEDIFF(YEAR, DOJ, GETDATE()) AS EXPERIENCE,
EMPSTATUS AS JOB_STATUS
FROM EMPLOYEE


DOJ - field in db for saving 'date of joining' of employee.
This is the query which returns experience in years only. How to modify it?

Answer
SELECT 
    EMPID, EMPNAME, DEPARTMENT, DESIGNATION, 

    convert(varchar(3),DATEDIFF(MONTH, DOJ, GETDATE())/12) +' years '+
    convert(varchar(2),DATEDIFF(MONTH, DOJ, GETDATE()) % 12)+ ' months' 
    AS EXPERIENCE, 

    EMPSTATUS AS JOB_STATUS 
FROM EMPLOYEE