Rohit Rohit - 1 month ago 7
MySQL Question

Want to display only those dates whose month is '10' using Mysql

I Have one column in the table called "date". In this column there are some dates stored from different months. I want to display only those dates whose month is '10'. How can we write query to print the desired output using substring() function of mysql ? Or else any other solution for this ?

enter image description here

O/p : first 8 records from this column.

Answer

MySQL has a lot of options for working with dates. In this case, combining the MONTH() function with STR_TO_DATE() would be easiest; MONTH() takes a date as input and returns the month number, starting at 1 for January, while STR_TO_DATE() will format your (non-standard) date string into a date MySQL understands.

Your query would then become:

SELECT * FROM your_table WHERE MONTH(STR_TO_DATE(`date`, '%d-%m-%Y')) = 10;