CODEWITHSUNDEEP
phpmysqlcodeigniter
Deepak
Deepak

Reputation: 53

get month & year from Mysql Date format m/d/y & compare with today's month & year

I have mysql date format m/d/y as varchar like 03/12/2015 for 12 March 2015. Now I want to count all the result from mysql table in which the month & year are the same as of today's date.

I am using following query, but no sucess

SELECT * FROM student where DATE_FORMAT(regd_date,'%m/%y')=DATE_FORMAT(now(),'%m/%y')"

Thanks for any help

Upvotes: 4

Views: 540

Answers (1)

Jason McCreary
Jason McCreary

Reputation: 73001

You will first need to use STR_TO_DATE() to convert your varchar column to a date before using DATE_FORMAT().

SELECT * FROM student
WHERE DATE_FORMAT(STR_TO_DATE(regd_date, '%m/%d/%Y'),'%m/%y') = DATE_FORMAT(now(),'%m/%y')

Adjust the format for STR_TO_DATE() accordingly.

Note: I would discourage storing dates as varchars.

Upvotes: 2

Related Questions