Problem with Between in Mysql?

Question

In an input and output control system there is a table cad_entrada_saida where it has two columns in the bank dEntrada and dSaida, these two columns are like varchar and recording the data in the format dd/mm/yyyy H:i defined by the former Developer.

When performing the SELECT at this table using BETWEEN dates are returned outside the defined range.

SELECT * FROM cad_entrada_saida 
    WHERE dEntrada BETWEEN '01/01/2017 06:00' AND '01/01/2017 23:00'

I’ve tried using the Function str_to_date

SELECT * FROM cad_entrada_saida WHERE dEntrada 
   BETWEEN str_to_date('01/01/2017 06:00', '%d/%m/%Y %H:%i') AND
           str_to_date('01/01/2017 23:00', '%d/%m/%Y %H:%i')

Both queries return me dates outside this range.

Answer 1

If dEntrada and dSaida are with the data type varchar, they need no converts to date in SQL also, that is, these fields should also use str_to_date, observe:

SELECT * FROM cad_entrada_saida 
WHERE str_to_date(dEntrada, '%d/%m/%Y %H:%i') BETWEEN 
      str_to_date('01/01/2017 06:00', '%d/%m/%Y %H:%i') AND
      str_to_date('01/01/2017 23:00', '%d/%m/%Y %H:%i')

_References:

• str_to_date
• 13.7 Date and Time Functions
• 12.3.7 Conversion Between Date and Time Types

Answer 2

The columns must be of the datetime.

In case you want to keep the current type (varchar), will have to do a cast.

I suggest to use in conjunction with the function UNIX_TIMESTAMP().

Below, just the snippet that matters, the conditional WHERE

UNIX_TIMESTAMP(STR_TO_DATE(dEntrada,'%d/%m/%Y %H:%i'))
BETWEEN UNIX_TIMESTAMP('2017-01-01 06:00:00') 
AND UNIX_TIMESTAMP('2017-01-01 06:23:00')

The input must be in ISO 8601 format (yyyy-mm-dd). I forgot to switch when I edited the reply.

Performance is not the issue here. In fact, if we’re going to treat this as a performance issue, the correct answer is @Upti’s because it solves both problems in one.

The reason you posted with UNIX_TIMESTAMP() is to avoid posting the same as others have posted, so it’s an alternate medium and another reason is, although the @Upti response is the most appropriate, depending on the case can be a nightmare. For example, if the system in general needs the column dEntrada is really a varchar, a change for the type datetime could generate several bugs in the system as a whole. A huge headache, time cost to adapt, etc. Sometimes a cast can be better as a temporary gambiarra.

Of course, over time you must fix and design the structure properly and consistently.

Answer 3

Columns of the type varchar cannot be consulted with a intermission.

You must convert your table to type datetime then consult the records by setting a range. Try using the same pattern as the Mysql: ('Y-m-d H:i:s')