Data Range Search in SQL

Asked

Viewed 394 times

2

I have a table with the structure below

inserir a descrição da imagem aqui

I need to make an appointment with a certain interval in order to generate a report. I used the Between command... and with it it returns me the following values

Valores de Retorno

However for the interface I need to present in the form dd-mm-yyyy and not as the saved seat which is yyyyyy-mm-dd.

I’ve used the remote

select * from venda where data_venda = DATE_FORMAT('18-01-2016', '%d-%m-%Y')

SELECT * FROM venda WHERE STR_TO_DATE(data_venda, '%d-%m-%Y') BETWEEN STR_TO_DATE('2016/01/18','%d-%m-%Y') AND STR_TO_DATE('2016/02/15','%d-%m-%Y')

But always returns a null value.

Has anyone there ever done that and could help me?

mysql
  • 2

    There is a confusion in the question. One thing is the format to show on the screen, which is what comes after SELECT and before FROM. Another is what you use in research, in WHERE. If you want to display differently, exchange that asterisk for the desired field with the desired formatting, and let WHERE work in the correct format ( yyyy-mm-dd ). How to search between two dates has already been answered here: http://answall.com/questions/4177/

  • Could you describe how you would look out of kindness? What I need is for you to return to the correct format because I am filling a jTable with the result of select.

  • SELECT DATE_FORMAT( data_venda, '%d-%m-%Y') AS datavenda FROM venda WHERE data_venda BETWEEN '2016-01-18' AND '2016-02-15' To use more fields, you can put before the DATE_FORMAT, or after the AS data sale, separating with comma

3 answers

4


Basically this:

SELECT DATE_FORMAT( data_venda, '%d-%m-%Y') AS datavenda
  FROM venda
  WHERE data_venda BETWEEN '2016-01-18' AND '2016-02-15'

To use more fields:

SELECT campo1, campo2, DATE_FORMAT( data_venda, '%d-%m-%Y') AS datavenda, campo3
   ...etc...

The AS datavenda is the name you want to use as a return to the query. Preferably, use a name that is not repeated as a table field, to avoid confusion.

You can even use the asterisk, but the field will come unformatted in the asterisk, and formatted at the end:

SELECT *, DATE_FORMAT( data_venda, '%d-%m-%Y') AS datavenda
  FROM venda
  WHERE data_venda BETWEEN '2016-01-18' AND '2016-02-15'

But not recommend. It is always good to specify only the fields you will use.

  • It helped a lot... Thank you have a good day!!!

1

Just in your select you specify and format the output with the desired pattern example:

SELECT 
    id_venda,
    DATE_FORMAT(data_venda,'%d-%m-%Y'),
    valor_venda,
    id_cliente
FROM venda

The downside is that you will need to specify the Fields however I do not know another way to get your result.

NOTE: I didn’t put your Where clause because I didn’t know which of the two you intend to use yet they don’t need to be modified.

1

You are formatting the date wrongly in WHERE condition. Just remove the formatting.

To receive the formatted date, do this in the SELECT snippet. Example of correction:

select 
id_venda,
DATE_FORMAT(data_venda, '%d-%m-%Y') as data_venda,
valor_venda,
id_cliente
from venda where data_venda = '2016-01-18'

The same thing in the second query. Fix by following the same logic as the above example.

Browser other questions tagged mysql

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