Compare month and year in postgres

Question

I'm trying this:

select * from venda where to_char(data_venda, 'MM-YYYY') = '07/2017'

Nothing returns.

The database does have two records with this date:

'21/07/2017'.

Answer 1

Use the same separator:

select v.*
from venda v
where to_char(v.data_venda, 'MM-YYYY') = '07-2017';

There are other ways to write this that don't involve changing data types:

where date_trunc('month', v.data_venda) = '2017-07-01'
where v.data_venda >= '2017-07-01' and v_data_venda < '2017-08-01'

The latter is nice, because it can readily use an index.