CODEWITHSUNDEEP
mysqldatetimedatetime-format
Lemon Kazi
Lemon Kazi

Reputation: 3311

mysql extract month from date format

My data value like below

          date_time
        ----------------        
        24:Sep:2016 22:19:35
        22:Oct:2016 22:19:35
        26:Sep:2016 22:19:35
        28:Sep:2016 22:19:35

Here I am using a query to check data where matched by month like below.

SELECT * FROM `audit_log` WHERE MONTH(STR_TO_DATE(date_time, '%d:%M:%Y %H:%i:%s')) ='Oct'

But this not working for me. But for year if I use that working

SELECT * FROM `audit_log` WHERE YEAR(STR_TO_DATE(date_time, '%d:%M:%Y %H:%i:%s')) ='2016'

Upvotes: 1

Views: 695

Answers (1)

Lemon Kazi
Lemon Kazi

Reputation: 3311

I got my answer. I used long form month to search and in query used MONTHNAME

SELECT * FROM `audit_log` WHERE MONTHNAME(STR_TO_DATE(date_time, '%d:%M:%Y %H:%i:%s')) ='October'

in different process I can use MONTH though it's return number. I have to use month number to search in query. like bwlow

SELECT * FROM `audit_log` WHERE MONTH(STR_TO_DATE(date_time, '%d:%M:%Y %H:%i:%s')) ='10'

Upvotes: 3

Related Questions