Bring data postgresql data field recorded as string

Question

Bring data postgresql data field recorded as string

Navigation

#1 by (0 votes)

0

I wonder if it's possible to convert this data to select time

Finding this data brings wrong information:

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

I would like to know if you can do this:

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

The database I'm using is PostgreSql and this date field is being written as string .

postgresql

asked by anonymous 27.11.2016 / 23:25

1 answer

getch python returning without using Enum returning me null pointer

score 0 · Answer 1

If the data_venda field does not have time_zone , you can convert the dates you have in string to date , using TO_DATE and then convert to American format using TO_CHAR like this:

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');

If the data_venda field has time_zone , you can do this:

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:

No 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');