CODEWITHSUNDEEP
mysql
tvgemert
tvgemert

Reputation: 1486

Get all unique years from a date column using SQL (MySQL)

I have a MySQL table with a datetime column named 'created' which contains the date and time the record was inserted. This table has about 30 records right now. I want to get all occuring years (unique, not per record) from this table. Is there a way to do this using a MySQL query?

Upvotes: 29

Views: 39864

Answers (6)

Cristian Girlea
Cristian Girlea

Reputation: 51

tableName::whereNotNull('columnName')->distinct()->get([DB::raw('YEAR(columnName) as year')]);

This is select distinct year from a date column in laravel. I added whereNotNull because in my database the date is not a requirement.

Upvotes: 0

J Bettis
J Bettis

Reputation: 21

SELECT DISTINCT YEAR(Date) AS MYR FROM YOUR_DBF_HERE ORDER BY MYR

Upvotes: 2

Sukanya1991
Sukanya1991

Reputation: 776

Select 
EXTRACT(year from your_column_name) as MYEAR from your_table_name
group by EXTRACT(year from your_column_name)

The above worked for me...

Upvotes: 1

Sukumar
Sukumar

Reputation: 3587

Something on these lines should work (Warning: untested code ahead)

select year(created) as year, count(*) as count from table_name group by year

This will also give number of records for each year.

Upvotes: 3

Quasdunk
Quasdunk

Reputation: 15220

SELECT EXTRACT(year from created) as year
FROM your_table
GROUP BY year

should work :)

Upvotes: 2

Dan Grossman
Dan Grossman

Reputation: 52372

SELECT DISTINCT YEAR(created) FROM table

Upvotes: 59

Related Questions