Convert Month Number to Month Name in PostgreSQL

by

You can use the following code examples in PostgreSQL if you have a month number but you want the month name instead.

Example 1: Full Month Name

Here’s a basic example of converting a month number to its corresponding month name.

SELECT TO_CHAR(
    TO_DATE (12::text, 'MM'), 'Month'
    ) AS "Month Name";

Result:

 Month Name 
------------
 December

Example 2: Short Month Name

You can also convert it to the short month name. To do this, replace 'Month' with 'Mon'.

SELECT TO_CHAR(
    TO_DATE (12::text, 'MM'), 'Mon'
    ) AS "Month Name";

Result:

 Month Name 
------------
 Dec

Example 3: Using Timestamp

Here’s an example that uses TO_TIMESTAMP() instead of TO_DATE().

SELECT TO_CHAR(
    TO_TIMESTAMP (12::text, 'MM'), 'Month'
    ) AS "Month Name";

Result:

 Month Name 
------------
 December

Example 4: Case-Sensitivity

The template pattern is case-sensitive, so you can use it to specify whether the month name should use lowercase, uppercase, or be capitalised.

SELECT 
  TO_CHAR(
    TO_TIMESTAMP (12::text, 'MM'), 'mon'
    ) AS "mon",
  TO_CHAR(
    TO_TIMESTAMP (12::text, 'MM'), 'Mon'
    ) AS "Mon",
  TO_CHAR(
    TO_TIMESTAMP (12::text, 'MM'), 'MON'
    ) AS "MON";

Result:

 mon | Mon | MON 
-----+-----+-----
 dec | Dec | DEC