Using CONCAT in combination with SUBSTR

Question

I want to write a select that would return me a distinct years only (2018, 2017,2016).

I have column AS_OF_DATE in table HISTORY.

Here are some example values of AS_OF_DATE:

31-05-18,
31-04-17,
31-07-16,
...

This is what I tried, but it doesn't work:

SELECT CONCAT('20',DISTINCT SUBSTR(AS_OF_DATE, 7, 2) FROM HISTORY

I used CONCAT to add 20 in front of the result and SUBSTR that would start at the 7th string and would be 2 strings long (so I get 18,17,16...)

Answer 1

try like below

SELECT DISTINCT '20' || SUBSTR(AS_OF_DATE, 7, 2) FROM HISTORY