1
I own a field of the kind datetime and I need to make a consultation based only on the month and year. While researching, I noticed that there is the function extract, but it separates only one value from the datetime.
example:
SELECT * FROM mytable
WHERE EXTRACT(month from date_column) = 5
AND EXTRACT(year from date_column) = 2018;
Is there any function that does this "Extract" instead of using the AND?
1
You can use the TO_CHAR in the date field, passing the format only to return the month and year, as an example 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;
/
Follows the result of the execution:
You can also use TO_CHAR by spending the month and year at the same time (as below), so 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!
0
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: worth giving a read in this question and understand why not use functions like the EXTRACT in the where.
but what’s the problem? if the date should be 5 months and 2018, it should obligatorily be greater/equal 01/05/2018 and less/equal 31/05/2018, no?!
reread the question.
read.. reread.. what I saw q could change was the fixed date; but I believe this does not invalidate the answer, since in the question it is not parameterized.. no further, than I understand, the answer is this! if I’m wrong, please edit the question or comment something that helps, since no one answered!!
it a function that does everything at once type WHERE date_column = '2018-05'
understood, but also do not know any function type.. the closest (considering performance) I believe that would be what answered
Browser other questions tagged sql postgresql
You are not signed in. Login or sign up in order to post.
to do exactly what EXTRACT does in its simplicity I do not know, but I also see no reason to have. you could use a sub string and compare a part of the date plus it arrive and be a gabiarra comparing what EXTRACT does.
– Marco Souza
You can use the date_trunc('Month', seu_campo_timestamp function).
– anonimo