In the database, the date is stored in the text field, in the format of the form: 17.07.2016
I do a sample:

 SELECT COUNT(`sms_send_exam`) FROM cartoteka WHERE `date_exam` BETWEEN '01.07.2000' AND '30.07.2025'

I get 18 (actually)

When I do this:

 SELECT COUNT(`sms_send_exam`) FROM cartoteka WHERE `date_exam` BETWEEN '29.07.2000' AND '30.07.2025'

I’m already getting 11 (!), This is an incorrect result.
Or for BETWEEN need a certain data format, type?

Addition. I tried the request

 SELECT COUNT(`sms_send_exam`) FROM cartoteka WHERE `date_exam` BETWEEN STR_TO_DATE('01.07.2000', '%d.%m.%Y') AND STR_TO_DATE('30.07.2025', '%d.%m.%Y')

It did not help either. And for some reason gives 9 (!)

Supplement_2 (!)
But such an option, through LIKE ( checked - working ), is it scary or normal?

 $sms_counter_month = mysql_query ( "SELECT COUNT(`sms_send_exam`) FROM ".$userstable." WHERE `date_exam` LIKE '%".$month.".".$year."'", $db);
  • 2
    And if you do WHERE STR_TO_DATE('date_exam', '%d.%m.%Y') BETWEEN STR_TO_DATE('01.07.2000', '%d.%m.%Y') AND STR_TO_DATE('30.07.2025', '%d.%m.%Y') ? (only correct quotes on date_exam put) - Batanichek
  • one
    And why do you keep the date in the text box. Make a date type field and you will be happy - Mike
  • You missed the date_exam to date format in the first addition, probably therefore your request does not work. - Powar
  • @Mike - the software was compiled without TZ, In the request for software there was only sending SMS by the date entered by the employee. But you know how it happens. After 4 months, the manager (if only he had to check everything), a Wishlist was born, receiving mini-reports on sent SMS, by months, and translating the date (changing the field) is more difficult for me than programming in LIKE. Or is converting fields from type to type fast and painless for a database? Somehow I did not have to indulge in this method. - I_CaR
  • Redo the field to the correct type - artoodetoo

1 answer 1

Your date is stored as the string '29 .07.2000 ', and the lines are compared first, i.e. it turns out you first compare days, then months, and only then years. Thus, the date of '29 .07.2000 'you will have more than '19 .07.2016'. In order to prevent this, it is better to store the date in the calendar data type DATE or at worst in the form of the string '2000.07.29', when you have the first year, followed by a month and then only a day.

Your query with LIKE also has a right to exist and is quite often used, although I repeat with the date it would be much more efficient to use the calendar type. Unfortunately, you have the% template here at the beginning of the line, so even if you index it, this will not help you much, since the lines are compared from the beginning, from the first characters.