0

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

sen
  • 91
  • 7
  • Please add sample data for both date columns. – Tim Biegeleisen Jan 10 '22 at 03:56
  • @TimBiegeleisen thanks for reply, above shown `dob` is date column and other is the datestring `userdate=02 Oct 1992`, i need to write query whether datecolumn and datestring is exact match, (for datestring i donknow how to convert to format yyyy-mm-dd and check with dob) – sen Jan 10 '22 at 04:13
  • `str_to_date` needs in a pattern to convert **from**, not to convert **to**. rather than `date_format()`. – Akina Jan 10 '22 at 05:16

1 Answers1

0

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
Gab
  • 3,404
  • 1
  • 11
  • 22