MySql Date Into Year, Date Into Month, Date Into Day

If you have a bulk amount of data in MySql, which includes the date or datetime field, and you want to view it as Yearly, Monthly or Daily basis, for example, if you want to view the total number of members, registered in a year, month or day, then the following queries might be helpful. In the following queries, we will be using the table as tbl_date, where id is the auto increment field, and inserted_date is the date / datetime field.

View Date Into Years In MySql

For viewing the date into years, the following query will be helpful:

SELECT count(id), DATE_FORMAT(inserted_date,"%Y") as years
FROM tbl_date
GROUP BY years
ORDER BY inserted_date;

View Date Into Months In MySql

For viewing the date into months, the following query will be helpful:

SELECT count(id), DATE_FORMAT(inserted_date,"%M") as months
FROM tbl_date
GROUP BY months
ORDER BY inserted_date;

View Date Into Days In MySql

For viewing the date into days, the following query will be helpful:

SELECT count(id), DATE_FORMAT(inserted_date,"%W") as days
FROM tbl_date
GROUP BY days
ORDER BY inserted_date;