78

I want to select rows according to the month of a date or timestamp column like this:

SELECT id, name, birthday 
FROM employee.person 
WHERE Month(birthday) > 10;

But I only get error messages in PostgreSQL.
How can this be done?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
aulia
  • 801
  • 1
  • 8
  • 11

2 Answers2

151

You can use EXTRACT function, like this:

SELECT id, name, birthday FROM employee.person 
WHERE EXTRACT(MONTH FROM birthday) > 10;

Your problem comes from the fact that there is no such thing as Month function in PostgreSQL. Check online documentation here to see what you can get instead. Extract should be enough.

k.m
  • 30,794
  • 10
  • 62
  • 86
  • I got HINT: No function matches the given name and argument types. You might need to add explicit type casts. – Luffydude Sep 26 '18 at 08:45
  • 3
    The downside of this method it cannot utilise an index on the birthday column. – Fluous Sep 07 '20 at 14:57
  • this hint really worked for me, just being careful that month = '08', is not an integer, therefore must be quoted. – Vlad Dec 12 '22 at 18:15
1

If you want you can also extract the month name using the following function.

SELECT TO_CHAR(DATE(REPORT_DATE), 'Month') FROM TABLE_NAME
Anish Jain
  • 509
  • 5
  • 12