Get all month from colums sql in java-CodePudding

I have table "TableHome", column name "Date" in database "DatabaseHome.db" contains data like this

5 January 2021
6 October 2021
10 December 2021

I want to take the month name for each year to generate monthly statistics, what sytax should I use in sql? and how to have that data converted into arraylist in java without duplicate items?

CodePudding user response：

To convert the above dates to month int values use

SELECT month(str_to_date('10 December 2021', '%d %M %Y')) as month;

To summarise for a month you need to sum(or count depending what you want) and group by column one,

so something like

SELECT month(str_to_date(dateCol, '%d %M %Y')) as month, 
                         sum(someOtherCol) from mytable group by 1;

CodePudding user response：

You should try with MONTHNAME(DATE) to get Month in String and DISTINCT to remove dupilcate items

select distinct(monthname(str_to_date(col1, '%d %M %y'))) as month
from table1

dbfiddle here