In PostgreSQL, if you already have a month name, but you want to convert that name to the month number, you can do this with the EXTRACT() function.
Example 1: Full Month Name
Here’s a basic example of converting a month name to its corresponding month number.
SELECT EXTRACT(MONTH FROM TO_DATE('December', 'Month')) AS "Month Number";
Result:
Month Number
--------------
12
Example 2: Short Month Name
It also works on short month names. In this case, just shorten the template string from 'Month' to 'Mon' (second argument of the TO_DATE() function).
SELECT EXTRACT(MONTH FROM TO_DATE('Dec', 'Mon')) AS "Month Number";
Result:
Month Number
--------------
12
Example 3: Longer Dates
It also works when you provide a longer date (not just the month name).
SELECT EXTRACT(
MONTH FROM TO_DATE('December 20, 2020', 'Month')
) AS "Month Number";
Result:
Month Number
--------------
12
Example 4: Using Timestamp
Here’s an example using TO_TIMESTAMP() instead of TO_DATE().
SELECT EXTRACT(MONTH FROM TO_TIMESTAMP('Dec 2020', 'Mon')) AS "Month Number";
Result:
Month Number
--------------
12