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