Drake Drake - 5 months ago 16
MySQL Question

how to select month and year in from dd/mm/yyyy 00:00 format mysql

I have datetime in mysql as 20/09/2016 10:00

How can I select only month and year?

I have tried multiple things suggested but not working.

Any ideas?


Choosing the right datatype has much gravity.

Since you are storing date time in varchar datatype I would suggest to move it to the timestamp type.

But before that you need to change the format of your date time value in a standard one (yyyy-mm-dd hh:mm:ss).

First, update the date time values to the above format:

SET column1 = STR_TO_DATE(column1,'%d/%m/%Y %H:%i');

Now change the datatype to timestamp

ALTER TABLE your_table MODIFY column1 timestamp;

Now execute your query:

select date_format (column1, '%m/%Y') from table1