I have a field of type datetime and I need to do a query based only on the month and year. When searching, I noticed that there is a extract function, but it separates only one value from datetime .
example:
SELECT * FROM mytable
WHERE EXTRACT(month from date_column) = 5
AND EXTRACT(year from date_column) = 2018;
Is there a function that does this "extract" at one time rather than using AND ?
You can use TO_CHAR in the date field, passing the formatting only to return the month and year, as shown below:
DECLARE
data_exemplo DATE := TO_DATE('10/01/2005','DD/MM/YYYY');
data_exemplo_2 DATE := TO_DATE('13/03/1998','DD/MM/YYYY');
v_nome VARCHAR(30);
BEGIN
SELECT NOME
INTO v_nome
FROM PESSOA
WHERE TO_CHAR(data_nasc,'MM') = TO_CHAR(data_exemplo,'MM')
AND TO_CHAR(data_nasc,'YYYY') = TO_CHAR(data_exemplo_2,'YYYY');
DBMS_OUTPUT.PUT_LINE('Valor retornado: ' || v_nome);
END;
/
The result of the execution follows:
You can also use TO_CHAR by passing the month and year at the same time (as below), then it would depend on your need.
SELECT NOME
INTO v_nome
FROM PESSOA
WHERE TO_CHAR(data_nasc,'MM/YYYY') = TO_CHAR(data_exemplo,'MM/YYYY');
Good studies!
The best way to do this would be to check the interval between dates.
SELECT *
FROM mytable
WHERE date_column >= '2018-05-01'
AND date_column <= '2018-05-31';
detail : give a read in this question and understand why not use functions type EXTRACT in where .