How to compare two dates saved in string format

I am working on an application which requires to compare two dates and fetch data between them. But the problem is the date is saved in “%m/%d” format (not “%m/%d/%Y”) as string. I’ve tried many different methods but not succeeded. I’m using PHP & mySql.

Do you have enough permissions to modify the database structure?
If yes, I would add year to those dates first (with single query) and then convert column type to DATE instead of string

I don’t have permission to modify database structure :pensive:

And you can’t even create a new table to store reformatted data from the current one?

yeah, you are right.

Is it achievable? Is there anyone to help?

use     STR_TO_DATE(CONCAT(stringcolumn,‘/2015’),‘%m/%d/%Y’)

@r937, thanks for your reply. I used your method, but it returns empty array:

“SELECT post_id FROM postmeta WHERE meta_key=‘_ct_birthday’ AND STR_TO_DATE(CONCAT(meta_value,‘/2000’), ‘%m/%d/%Y’) BETWEEN ‘8/15’ AND ‘12/15’”

But there is data for “12/10” date. It should return at least one row. Am I doing it wrong way?

[quote=“saddam987020, post:8, topic:200807, full:true”] Am I doing it wrong way?

when you convert to a date, you have to compare to actual dates

AND STR_TO_DATE(CONCAT(meta_value,'/2000'),'%m/%d/%Y') BETWEEN '2000-08-15' AND '2000-12-15'

1 Like

@r937 you are superb!! Worked like a charm :relaxed:

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.