Extract Years from multiple columns and prepare a consolidated list

Question

Hi Team, 
 I have an Employee table in below structure, and I want to create a list of years by taking all the distinct years from both the Start_Date and End_Date columns. 
 employee ( emp_id Number, Start_Date Date, End_Date Date, Status Varchar2(50) ) 
 Please help me with your ideas.

Andrew Zysk · Accepted Answer

Hi Sunil, 
 
There are 2 steps to this solution: 
1. Get the list of all the years, given the Start_Date and End_Date fields 
2. Get the distinct years from the list yielded by step 1. 
 
Details: 
 
1. You can use the year() function to extract the year as an Integer from a Date. To do this over an array of dates, a!forEach should work fine. 
 
2. A common method to get the distinct values (remove duplicates) from a list is to use a union() function, using the same list as both arguments. This function will return the list without duplicates. It is a good idea to make a general "GBL_distinct" rule to encapsulate this purpose.