Drake Drake - 2 months ago 8
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?

Answer

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:

UPDATE 
your_table 
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
Comments