Make a select in a varchar column as if it were SQL datetime

Asked

Viewed 891 times

1

The code is as follows::

    SELECT data 
    FROM lista 
    WHERE data BETWEEN '01-08-2015' 
    AND '05-08-2015';

The problem is that the date column is a varchar and I would like to use it as a date

php mysql sql phpmyadmin
  • 1

    Try to be clearer in your question

  • The date column is varchar type. And I want to use the between in it as if it were datetime type.

  • And what mistake you made?

  • If I send select this way, it returns nothing to me. No errors, but it also does not look for values between dates because it is not a date. I have to convert this field to datatime type before I select.

1 answer

2

If it is SQL you can use the function convert.

https://msdn.microsoft.com/pt-br/library/ms187928(v=SQL.120). aspx

SQL

SELECT data 
FROM lista 
WHERE data BETWEEN convert(datetime,'01/08/2015', 103)
AND convert(datetime,'05/08/2015',103);

Mysql

SELECT data 
FROM lista 
WHERE data BETWEEN DATE_FORMAT(STR_TO_DATE('01/08/2015', '%d/%m/%Y'), '%Y-%m-%d %H:%m:%s')
AND WHERE data BETWEEN DATE_FORMAT(STR_TO_DATE('05/08/2015', '%d/%m/%Y'), '%Y-%m-%d %H:%m:%s')

  • Diego It returns me the following error: #1064 - You have an error in your SQL syntax; check the manual that Corresponds to your Mysql server version for the right syntax to use near ''01/08/2015',103) AND CONVERT(datetime, '04/08/2015',103) ORDER by protocol ASC' at line 1

  • Put the entire query here to see how it is now. But you are using Sql Server or Mysql. If it is Mysql the function is another.

  • It returns me values between the month 07 and the month 08, when I specified only month 08. SELECT DATA FROM OS WHERE DATA BETWEEN DATE_FORMAT( STR_TO_DATE( '01-08-2015', '%d-%m-%Y' ), '%d-%m-%Y' ) AND DATE_FORMAT( STR_TO_DATE( '05-08-2015', '%d-%m-%Y' ) '%d-%m-%Y') LIMIT 0 , 30

  • 1

    Unfortunately mysql code did not work here. I had to change the column structure pro type datatime.

  • Which is much better. Working with the right data type is correct. Sometimes for some reason there is no way, but we must do everything possible to make the fields specific to the type of work that will be done. Number int, float, decimal. String varchar, text. Date datetime, timestamp, date. And so on. It helps you and helps the next one who comes to modify the system.

Browser other questions tagged php mysql sql phpmyadmin

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