How to view month and year only oracle sql

Question

So I’m making a list of exercises, and in a given one, you ask me to make a query that returns the total value of a column, and that those values be grouped by month and year, but the date column has day as well. how to display only (mm/yyyy).

select 
  max(nr_pedido), 
  sum(vl_total), 
  dt_locacao 
from loc_pedido_locacao
group by dt_locacao;

Answer 1

Mysql or Oracle? I will assume Oracle once it appears in the subject and that the field containing the date is dt_location.

If this field is of date type:

SELECT
  MAX(nr_pedido),
  SUM(vl_total),
  TO_CHAR(dt_locacao, 'YYYY-MM')
FROM loc_pedido_locacao
GROUP BY TO_CHAR(dt_locacao, 'YYYY-MM');

If the field is of type CHAR we have two hypothesis. Both assume that the date format is fixed. I will assume that the date format is 'YYYY-MM-DD'.

Convert from char to date and back to char:

SELECT
  MAX(nr_pedido),
  SUM(vl_total),
  TO_CHAR(TO_DATE(dt_locacao, 'YYYY-MM-DD'), 'YYYY-MM')
FROM loc_pedido_locacao
GROUP BY TO_CHAR(TO_DATE(dt_locacao, 'YYYY-MM-DD'), 'YYYY-MM');

Extract the date value directly from the string:

SELECT
  MAX(nr_pedido),
  SUM(vl_total),
  SUBSTR(dt_locacao, 0, 7)
FROM loc_pedido_locacao
GROUP BY SUBSTR(dt_locacao, 0, 7);