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...)
try like below
SELECT DISTINCT '20' || SUBSTR(AS_OF_DATE, 7, 2) FROM HISTORY
Normally, you would do this with extract() or to_char():
select extract(year from as_of_date) as yyyy
or
select to_char(as_of_date, 'YYYY') as yyyy
This assumes that as_of_date is a date, which is should be.
You can add select distinct if you want a result set with the distinct years.
You can use
SELECT CONCAT('20', SUBSTR(AS_OF_DATE, -2) ) as "Years"
FROM HISTORY
GROUP BY SUBSTR(AS_OF_DATE, -2)
ORDER BY "Years"
this will work:
select distinct extract(year from as_of_date) from History;
the extract function takes off the as_of_date provided it is a date datatype and distinct select only one for dates which are multiple times in the extract.
