Hello all,
I have a database which contains a field with a string representing a birth date called bdate. The string in the form of mm/dd/yyyy. No doubt that is certainly not the best, but that is how db was setup. DB contains over 60k records. Trying to setup function where the user pics two dates and gets back birthdays between date1 and date2, without regard to year. EX: user pics March 29th and July 16 and function displays all birthdays which are between the two.
1. first, can php meaningfully compare two strings in the form of mm/dd?
If that is the case, am thinking
query = "SELECT conum, lname, fname, bdate, address, city, state, zip FROM constituents WHERE SUBSTRING_INDEX(bdate, '/', 2) >= '$date1' AND SUBSTRING_INDEX(bdate, '/', 2) =< '$date2'";
Any suggestions would be appreciated.
trying to compare two strings in the form of mm/dd
Moderator: General Moderators
Re: trying to compare two strings in the form of mm/dd
Take a look at strtotime().
[Edit: No, I don't think that will do what you want, after all. You'll need to separately test for month and day, I'm afraid.]
[Edit: No, I don't think that will do what you want, after all. You'll need to separately test for month and day, I'm afraid.]
Last edited by califdon on Wed Mar 21, 2012 6:01 pm, edited 1 time in total.
Reason: Changed my advice.
Reason: Changed my advice.
Re: trying to compare two strings in the form of mm/dd
If the year is not included and if the months and days are both zero-padded then yes, you can compare them. Exactly like you would compare strings.
But you're talking about MySQL, not PHP. Luckily the answer is the same.
Tip: use BETWEEN.
But you're talking about MySQL, not PHP. Luckily the answer is the same.
Tip: use BETWEEN.
Code: Select all
SUBSTRING(bdate, '/', 2) BETWEEN '$date1' AND '$date2'