How to obtain records of a specific month in SQL by date

Question

I'm using Postgres 9.1 and try to get records from a table knowing the given month and should know if the date type date field contains the month.

This is my query:

SELECT consejo.numero,consejo.fecha FROM consejo WHERE MONTH(consejo.fecha) = 2

but I get the following error:

ERROR: There is no month function (date)
LINE 1: ... T consejo.numero, consejo.fecha council FROM WHERE MONTH (cons ...

HINT: No function matches the name and types of arguments. You may need to add explicit type conversion.

might be wrong?

You should compare using `BETWEEN` or `>` to make your query [SARGable](http://stackoverflow.com/questions/799584/what-makes-a-sql-statement-sargable) — Lukasz Szozda, Mar 15 '16 at 16:27
Are you sure you want to filter on just the month, and not the year and month? — Dan Bracuk, Mar 15 '16 at 16:32

score 3 · Accepted Answer · answered Mar 15 '16 at 16:26

3

In Postgresql there is no MONTH function available. You can use EXTRACT instead:

WHERE EXTRACT(MONTH FROM consejo.fecha) = 2

answered Mar 15 '16 at 16:26

Giorgos Betsos

71,379
9
63
98

score 0 · Answer 2 · answered Mar 16 '16 at 02:05

0

Another option is to use PostgreSQL's date_part() function:

 WHERE date_part('month', consejo.fecha) = 2

Look at the reference guide:

http://www.postgresql.org/docs/9.5/static/functions-datetime.html

answered Mar 16 '16 at 02:05

Rodrigo Hjort

301
3
5

How to obtain records of a specific month in SQL by date

2 Answers2