Select data with Between in Varchar format

Asked

Viewed 1,329 times

0

I am redoing the question because I could not get the desired result, I would like to select in the field date but the between is not working, I believe it is due to my date field being in VARCHAR format, I tested the code below:

("SELECT * FROM caixa WHERE data BETWEEN '01/05/2017' AND '31/05/2017'");

however he selected all records and not only those that had date between 01/05/2017 and 31/05/2017.

php mysql sql web-application

  • The format you are writing to varchar is dd/mm/yyyy ?

  • yes. is in the same format as the code above.

3 answers

4


The correct is to store dates in fields of the type date, datetime or timestamp, but if you can’t restructure your table, with the function STR_TO_DATE you can convert string for date. In your case it would look like this:

SELECT * FROM caixa WHERE STR_TO_DATE(data, '%d/%m/%Y') BETWEEN STR_TO_DATE('01/05/2017', '%d/%m/%Y') AND STR_TO_DATE('31/05/2017', '%d/%m/%Y');

To learn more about STR_TO_DATE click here.

3

Gambiarra

SELECT
    *
FROM
    caixa
WHERE
    DATE_FORMAT (
        DATA,
        '%Y-%m-%d %H:%i:%s') BETWEEN '2017-05-01 00:00:00' AND '2017-05-31 23:59:59'

The Right

Change the field type for a date field. You have several as mentioned in the other answer.

  • In case I change I will lose all records right? This gambit has some against? (besides the mess)

  • Leaving the field type as varchar complicates when you need to do checks as now. You have to keep breaking your head to convert the data. When working with the correct data this is avoided. Create a column on the side of the type timestamp.

  • Then you update to update this column with the correct date, converting the other column.

  • ok thanks for the tip, but in case it would be in date format and it would save in yyyy-mm-dd format right?

  • That’s right. I would save that way, with hour, minute and second...

-2

SELECT * FROM caixa WHERE cast(data as date) BETWEEN '01/05/2017' AND '31/05/2017'

  • That one CAST does not work because the content of the date field is in format DD/MM/AAAA, in this case the CAST returns NULL.

  • Works on SQL Server, does not work even pro Mysql

Browser other questions tagged php mysql sql web-application

You are not signed in. Login or sign up in order to post.