Postgresql query without data return

Asked

Viewed 574 times

2

See the following SQL code:

SELECT * FROM venda ve WHERE ve.datavenda <= DATE '28/08/2016'

In the database you have the following data:

registro na referida tabela

The above SQL query is not returning any results, why? Could you help me? I appreciate it now!

sql postgresql date

  • neither need use date the field is already in time Stamp SELECT * FROM venda WHERE datavenda <= '2016-08-28'

  • No, I don’t see any mistake! This is correct @stderr

  • What is the expected return?

  • 1

    Can I ask you a question of personal curiosity? Why do people write "Postgree"? I understand some confusion with the name, but I never understood this one. That’s not the product name, nickname, abbreviation, nothing. What does it mean?

  • @bigown I know there is something close to what he commented, but at the time of pronunciation in English, which if written in Portuguese would be close to "postgree".

2 answers

3

This happens because field datavenda table vendas is the type timestamp, your query is being interpreted as follows:

SELECT * FROM venda ve WHERE ve.datavenda <= '28/08/2016 00:00:00.000000';

What does not include the records 28/08/2016 02:33:36.372 and 28/08/2016 02:55:46.873, and explains the behaviour described.

There are two possible solutions to your problem: 

SELECT * FROM venda ve WHERE ve.datavenda <= '28/08/2016 23:59:59.999999';

or

SELECT * FROM venda ve WHERE ve.datavenda::date <= '28/08/2016';

I hope I’ve helped!

  • Thank you, that’s right! My most sincere thanks!

1

Change your query to;

SELECT * FROM venda ve WHERE ve.datavenda::date <= '28/08/2016'

  • Thank you! You helped a lot!

Browser other questions tagged sql postgresql date

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