How to compare two dates saved in string format

Databases
1

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.

2

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

3

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

4

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

5

yeah, you are right.

6

Is it achievable? Is there anyone to help?

7

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

8

@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?

9

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

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
10

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

11

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