Problem in query with Between in MySQL?

Navigation
3

In an input and output control system there is a cad_entrada_saida table where you have two columns in the dEntrada and dSaida database, these two columns are as varchar , and writing the data in the format dd/mm/yyyy H:i in> defined by the former Developer .

Performing SELECT on this table using BETWEEN returns dates 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 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 dates outside of this range.

    
asked by anonymous 03.05.2017 / 04:54

3 answers

2

If dEntrada and dSaida are with data type varchar , they need no converters for date in SQL also, these fields should also use str_to_date , note: 

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:

03.05.2017 / 05:21
1

Columns must be of type datetime .

If you want to keep the current type ( varchar ), you'll have to cast it.

I suggest using in conjunction with the UNIX_TIMESTAMP() function.

Below, only the part 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 entry must be in ISO 8601 format (yyyy-mm-dd). I forgot to change when I edited the answer.

As for performance, that's not the point here. By the way, if we treat this as a performance issue, the correct answer is @UpTI because it solves both problems in one.

The reason for posting with UNIX_TIMESTAMP() is to avoid posting the same as others have already posted, so it remains as an alternative medium and another reason is, although @UpTI's answer is the most appropriate, depending on the case it may be a nightmare. For example, if the system generally requires that the dEntrada column is even a varchar, a change to type datetime could generate several bugs in the system as a whole. A huge headache, cost of time to adapt, etc. Sometimes a cast might be better as a temporary gambiarra.

Of course, over time you should mend and draw the structure appropriately and consistently.

    
03.05.2017 / 05:26
0

Columns of type varchar can not be queried with a range .

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

    
03.05.2017 / 05:17
Download Javascript Array in a Combobox Persisting in batch in Many-to-Many Entity with extra fields