How to write query for date exact match in mysql

Question

I would like to know how to query to display the list

if date of user matches with the date dob in the table

user always have the dateformat as 02 Oct 1992 dd mm yyyy

How to convert the user date format(yyyy mm dd) and check the dobmatches in mysql query

I have date in table as

userdate = 02 Oct 1992

user dob
xyz  1992-10-02
abc  1986-02-06

SELECT * FROM datetable WHERE DATE(str_to_date(userdate, '%Y-%m-%d')) = datetable.dob

currently the query not working

Answer 1

This should do what you want:

SELECT *
FROM datetable
WHERE dob = STR_TO_DATE(userdate, "%d %b %Y");

Example of date parsing for userdate:

SELECT STR_TO_DATE("02 Oct 1992", "%d %b %Y") as date;
+------------+
| date       |
|------------|
| 1992-10-02 |
+------------+
1 row in set
Time: 0.005s