Interview Answer

Beware of bugs in the above code; I have only proved it correct, not tried it. Donald E. Knuth

Write a query to list the length of service of the Employees

January 1, 1970 12:00 am

Comments



Write a query to list the length of service of the Employees



												desc HR.employees;
												Name           Null     Type         
												-------------- -------- ------------ 
												EMPLOYEE_ID    NOT NULL NUMBER(6)    
												FIRST_NAME              VARCHAR2(20) 
												LAST_NAME      NOT NULL VARCHAR2(25) 
												EMAIL          NOT NULL VARCHAR2(25) 
												PHONE_NUMBER            VARCHAR2(20) 
												HIRE_DATE      NOT NULL DATE         
												JOB_ID         NOT NULL VARCHAR2(10) 
												SALARY                  NUMBER(8,2)  
												COMMISSION_PCT          NUMBER(2,2)  
												MANAGER_ID              NUMBER(6)    
												DEPARTMENT_ID           NUMBER(4)   
												

The to_char funcation convert date format to char and || append the string.


Solution

   					 
												SELECT first_name,last_name,TO_CHAR(TRUNC(MONTHS_BETWEEN(SYSDATE,HIRE_DATE)/12))
												||' YEARS '|| TO_CHAR(TRUNC(MOD(MONTHS_BETWEEN 
												(SYSDATE, HIRE_DATE),12)))||' MONTHS ' "LENGTH OF SERVICE"
												FROM HR.employees;
																    

Comments



Please login to add comments.