-1

I am trying to compare the following:

SELECT g_m.user_id
     , g_m.group_id 
  FROM Group_Members g_m 
 WHERE g_m.gender_id = 2 
   AND g_m.partner_gender_id = 1 
   AND g_m.birthday >= '01-01-1955' 
   AND g_m.birthday <= '12-31-2002' 
   AND g_m.user_id != 12

g_m.birthday in this case is '02-15-1998' which should show up, but this returns an empty array, because the date comparison does not seem to be accurate?

Here is the entire function and the dates are being passed from age minimum and age maximums brought from user.

var today = new Date();

var minYear = "01-01-" + (today.getFullYear() - userPref.age_max); //min year to start but oldest age
var maxYear = "12-31-" + (today.getFullYear() - userPref.age_min); //max year to end but youngest age
var qSelect = "SELECT g_m.user_id, g_m.group_id" +
                        " FROM Group_Members g_m WHERE g_m.gender_id = ? AND g_m.partner_gender_id = ?" +
                       " AND g_m.birthday >= STR_TO_DATE(?, '%m/%d/%Y') AND g_m.birthday <= STR_TO_DATE(?, '%m/%d/%Y')" + 
                        " AND g_m.user_id != ?";
  var qValues = [userPref.partner_gender_id, userObj.gender_id, minYear, maxYear, userObj.id];

Anyone know how to compare dates in a mysql query?

Shadow
  • 33,525
  • 10
  • 51
  • 64
Lion789
  • 4,402
  • 12
  • 58
  • 96

2 Answers2

0

What is the data type of your dates ? You should declare them as "date", otherwise you won't be able to compare them. With strings, '02-15-1998' < '03-15-1990'

With dates, your mysql request should be :

SELECT g_m.user_id, g_m.group_id FROM Group_Members g_m WHERE g_m.gender_id = 2 AND g_m.partner_gender_id = 1 AND g_m.birthday >= '1955-01-01' AND g_m.birthday <= '2002-12-31' AND g_m.user_id != 12

Sorry for my english, I'm french...

Olivier
  • 11
  • 1
  • 4
  • Hi please see my update above, I am using age range to build the dates, and with strtodate it doesnt work. – Lion789 Dec 17 '20 at 07:31
0

As the comments have already pointed out, you appear to be storing your dates as actual text. For a short term workaround, you may use STR_TO_DATE to convert your text dates to bona fide MySQL dates. Then, compare them against valid MySQL date literals:

SELECT
    g_m.user_id,
    g_m.group_id
FROM Group_Members g_m 
WHERE
    g_m.gender_id = 2 AND 
    g_m.partner_gender_id = 1 AND
    STR_TO_DATE(g_m.birthday, '%m-%d-%Y') >= '1955-01-01' AND
    STR_TO_DATE(g_m.birthday, '%m-%d-%Y') < '2003-01-01' AND
    g_m.user_id != 12;

Longer term, you should make the birthday column datetime or timestamp.

Side note: I have rewritten the date range to include those born in the calendar years from 1955 to 2002, inclusive on both ends.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • I tried this but still get nothing back, see my update above, I am using age range to build the dates, and with strtodate it doesnt work. – Lion789 Dec 17 '20 at 07:32
  • My query is correct (you are not using it in your edited question above BTW), and the problem is your data, not my answer. – Tim Biegeleisen Dec 17 '20 at 07:34
  • Ok if I switch it to Datetime how would I change the query then, I rather fix the long term problem – Lion789 Dec 17 '20 at 07:35
  • I have answered your question. The site doesn't work this way, where you keep changing the question and then everyone dances. If you need help with something else, then please just ask a new question. – Tim Biegeleisen Dec 17 '20 at 07:37
  • Ok sorry about that, will ask a new question then thanks for the help – Lion789 Dec 17 '20 at 07:38
  • Is the format correct for what you provided after greater than and less than symbols, because I see one format month/day/year but in the string after it is year/month/day? – Lion789 Dec 17 '20 at 08:02
  • Your text dates are stored (presumably) in the format `%m-%d-%Y`. MySQL valid date literals are `%Y-%m-%d`. – Tim Biegeleisen Dec 17 '20 at 08:05