group by month and year with this format "01/29/2016"

[Yohanne]

Hi coders,

 

 

how to group by a month and year if i have a data date format like this.

 

10/23/2014

12/23/2015

11/23/2016

01/23/2016

01/23/2016

06/23/2014

06/23/2014 

06/23/2014  

 

Thanks

[requinix]

Is this in a database?

[Yohanne]

Yes

[requinix]

Are you storing the dates as DATEs or VAR/CHARs? That's the type of the column. You need to be storing them as DATEs, and when you do you can use functions like MONTH, YEAR, and DATE_FORMAT.

[Yohanne]

i use VAR/CHARs and i think its too late to change it into DATE. its already have data more than 70k. 

[Yohanne]

do you have any other solution on it?

[Barand]

Use str_to_date() to convert your format to to DATE type. Then you can use the other datetime functions on it.

EG

SELECT
    YEAR(STR_TO_DATE(datestr, '%m/%d/%Y')) as yr
,   MONTH(STR_TO_DATE(datestr, '%m/%d/%Y')) as mth
,   COUNT(*) as total
FROM mytable
GROUP BY yr, mth

You can use the same function to update your database table with a single query.

• Backup the table, just in case
• UPDATE mytable SET datestr = STR_TO_DATE(datestr, '%m/%d/%Y');
• Alter varchar to DATE