PostgreSQL provides a wide range of built-in functions to work with date and time values, such as NOW(), EXTRACT(), DATE_PART(), etc. To get a specific date filed, the EXTRACT() and DATE_PART() functions are used in Postgres. However, these functions return the date field as an integer. To get a date field as a text the built-in TO_CHAR() function is used in Postgres.

This post presents an in-depth guide on getting the month name from a date or timestamp.

How Do I Get the Month’s Name From a Date in Postgres?

To get a month name from a date, specify the date/timestamp as the first and “MONTH” as the second argument to the TO_CHAR() function:

TO_CHAR(TIMESTAMP | DATE, 'Month');

Let's put this concept into practice.

Example 1: Getting Month Name From Date

In the following snippet, the TO_CHAR() function is used to get the month name from the given date:

SELECT TO_CHAR(DATE '2010-02-10', 'Month') AS month_name;

The TO_CHAR() function fetches the month name from the given date.

Example 3: Getting Month Name From Current Date

To get the month name from the current date, the CURRENT_DATE function and “Month” are passed as arguments to the TO_CHAR() function:

SELECT TO_CHAR(CURRENT_DATE, 'Month') AS current_month_name;

The stated function succeeded in getting the month name from the current date.

Example 3: Getting Month Names in Different Letter Cases

The below snippet demonstrates how to get the month names in different letter cases:

SELECT TO_CHAR(DATE '2010-07-10', 'Month') AS init_cap,
TO_CHAR(DATE '2010-07-10', 'month') AS lowercase,
TO_CHAR(DATE '2010-07-10', 'MONTH') AS uppercase;

Example 4: Getting Abbreviated Month Names

The following code depicts how to get an abbreviated month name in Postgres using the TO_CHAR() function:

SELECT TO_CHAR(DATE '2010-08-10', 'Mon') AS abbreviated_month_name;

The TO_CHAR() function successfully retrieves the abbreviated month name.

Example 5: Getting Month Names From Table’s Data

A sample table named “emp_data” has already been created, whose data is enlisted in the following snippet:

Let’s use the “TO_CHAR()” function to get the month names from the “joining_date” column:

SELECT emp_name, joining_date, TO_CHAR(joining_date, 'Month') AS joining_month
FROM emp_data;

The month names from the “joining_date” column have been retrieved successfully.

Conclusion

To get a month name from a date, specify the date/timestamp as the first and “MONTH” as the second argument to the TO_CHAR() function. The letter case for the extracted month name depends on the second parameter of the TO_CHAR() function. To get the abbreviated month name, pass the first three letters, such as “MON” as the second parameter to the TO_CHAR() function. This post presented a detailed guide on getting the month's name from a date using suitable examples.