CODEWITHSUNDEEP
mysqlsql
Багдаулет Сайын
Багдаулет Сайын

Reputation: 99

how to get a list of all years from the mySQL

+------------+---------------+------------+
| date       | other_columns | date2      |
+------------+---------------+------------+
| 2019-05-23 | #             | 2018-04-12 |
+------------+---------------+------------+
| 2013-04-08 | #             | null       |
+------------+---------------+------------+
| 2007-11-11 | #             | 2019-09-13 |
+------------+---------------+------------+

there is such a table in mySQL, with several thousand rows. how can I get out of there only years without requesting the entire base. I just can’t figure out how to do this. 

[2019,
2018,
2013,
2007]

I would like to extract them something like this, I will be glad to any advice

Upvotes: 0

Views: 184

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271031

You can use year() and union all:

select distinct year(date)
from (select date from t union all
      select date2 from t
     ) t;

If you want this as a single concatenated string, I would recommend:

select group_concat(yyyy order by yyyy) as years
from (select year(date) as yyyy from t
      union -- intentionally remove duplicates
      select year(date2) from t
     ) t;

Upvotes: 2

Related Questions