Bring postgresql data field data saved as string

Asked

Viewed 185 times

0

I would like to know if it is possible to convert this data at the time of select

Looking for this data, brings wrong information:

select * from vendas where data_venda  between  '01/03/2016' and '16/10/2016';

I wonder if you can do it like this:

select * from vendas where data_venda  between  '2016-03-01' and '2016-10-16';

The database I am using is Postgresql and this date field is being recorded as string.

postgresql

  • Do date commands work, for example, BETWEEN even if the field is a String and not DATE? If yes, choose the second option, American format, has occurred to me several problems with the first example, using our date formatting.

1 answer

0

If the field data_venda have not time_zone, you can convert the dates you have into string for date, using the TO_DATE and then convert to American format using the TO_CHAR, thus:

SELECT  * FROM vendas 
  WHERE data_venda 
BETWEEN TO_CHAR(TO_DATE('01/03/2016', 'DD-MM-YYYY'), 'YYYY-MM-DD') 
    AND TO_CHAR(TO_DATE('16/10/2016', 'DD-MM-YYYY'), 'YYYY-MM-DD');

Case the field data_venda has been time_zone, you can do so:

SELECT  * FROM vendas 
  WHERE TO_CHAR(data_venda, 'YYYY-MM-DD') --Convertendo para o formato americano 
BETWEEN TO_CHAR(TO_DATE('01/03/2016', 'DD-MM-YYYY'), 'YYYY-MM-DD') 
    AND TO_CHAR(TO_DATE('16/10/2016', 'DD-MM-YYYY'), 'YYYY-MM-DD');

Simplified form:

Without timezone

SELECT  * FROM vendas 
  WHERE data_venda
BETWEEN TO_CHAR('01/03/2016'::DATE, 'YYYY-MM-DD') 
    AND TO_CHAR('16/10/2016'::DATE, 'YYYY-MM-DD');

With timezone

SELECT  * FROM vendas 
  WHERE TO_CHAR(data_venda, 'YYYY-MM-DD') --Convertendo para o formato americano 
BETWEEN TO_CHAR('01/03/2016'::DATE, 'YYYY-MM-DD') 
    AND TO_CHAR('16/10/2016'::DATE, 'YYYY-MM-DD');

Browser other questions tagged postgresql

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