If you’re familiar with PostgreSQL, you might know that you can use the EXTRACT() and the DATE_PART() functions to extract the month from a date. But those functions only allow you to extract the month number.

What if you need the month name?

You can get the month name from a date by using the TO_CHAR() function. This function returns a string based on the timestamp and the template pattern you provide as arguments.

Example

Here’s a quick example.

SELECT TO_CHAR(TIMESTAMP '2020-12-16 10:41:35', 'Month') AS "Month";

Result:

   Month   
-----------
 December   

In this case, I specified a template pattern of 'Month', so this caused the month to be returned from the timestamp value.

Lowercase, Uppercase, and Capitalisation

You can return the month in lowercase, uppercase, or capitalised. You determine which one by the case you use for the template pattern (second argument).

Here’s an example to demonstrate what I mean.

SELECT 
  TO_CHAR(current_timestamp, 'month') AS "month",
  TO_CHAR(current_timestamp, 'Month') AS "Month",
  TO_CHAR(current_timestamp, 'MONTH') AS "MONTH";

Result:

   month   |   Month   |   MONTH   
-----------+-----------+-----------
 march     | March     | MARCH    

All results are blank-padded to 9 chars, regardless of the case used.

Short Month Name

You can also specify that the result is formatted using the short month name.

SELECT 
  TO_CHAR(current_timestamp, 'mon') AS "mon",
  TO_CHAR(current_timestamp, 'Mon') AS "Mon",
  TO_CHAR(current_timestamp, 'MON') AS "MON";

Result:

 mon | Mon | MON 
-----+-----+-----
 mar | Mar | MAR