To get a date into a specific format, the TO_CHAR() function is used. Generally, the TO_CHAR() function is used to convert a timestamp, an interval, a numeric value, a double-precision, or an integer to a string.

In PostgreSQL, the CURRENT_DATE and NOW() functions are used to obtain the current date in YYYY-MM-DD format. To retrieve the current date in a particular format, use the Postgres TO_CHAR() function.

This write-up will thoroughly explain how to get a date into a specific format in Postgres. So, let’s get started!

How to Get a Date Into a Specific Format?

Follow the below syntax to format a date using the TO_CHAR() function:

TO_CHAR('date_value'::DATE, 'date_format');

Here, the date_vale represents a date to be converted while the date_format represents a format based on which the date_value will be formatted.

Example: How to Format a Date in “dd Month, yyyy” Format?

To format the given date into the specified format, run the following command:

SELECT TO_CHAR('2022-09-01'::DATE, 'dd Month, yyyy');

This way, you can get a date into a specific format.

How Does NOW() Function Work in PostgreSQL?

The NOW() function retrieves the latest time, date, and time zone. To get only the date part, we have to cast the date-time value to a date. To do that, follow the below syntax:

NOW()::DATE;

Example: How to Get the Current Date Using NOW() Function?

Let’s run the below statement to retrieve today’s date:

SELECT NOW()::DATE;

This is how the NOW() function works in Postgres.

What is the CURRENT_DATE Function and How to Use it in Postgres?

In PostgreSQL, the CURRENT_DATE function can be used to retrieve the latest date.

CURRENT_DATE;

Example: How Does the CURRENT_DATE Function Work in Postgres?

Run the below command to retrieve the current date:

SELECT CURRENT_DATE;

The output shows that the CURRENT_DATE function successfully fetched the current date.

How to Get a Current Date into a Particular Format Using TO_CHAR() Function?

The above examples proved that both NOW() and CURRENT_DATE functions return the date in “YYYY-MM-DD” format. To convert/retrieve the date into a particular format, use the Postgres TO_CHAR() function. The below snippet illustrates the basic syntax of the TO_CHAR() function:

TO_CHAR(expression, format);

- In the above snippet, the expression parameter represents a value to be formatted.

- The “format” argument represents a format according to which the given expression will be formatted.

Example #1: How to Get a Date in the “DD/MM/YYYY” Format?

Let’s display the current date in “DD/MM/YYYY” format using the TO_CHAR() function:

SELECT TO_CHAR(CURRENT_DATE, 'DD/MM/YYYY');

The output shows that the TO_CHAR() function converted the current date into “DD/MM/YYYY” format.

Example #2: How to Get a Date in the User’s Specified Format?

Specify “MONTH DD, YYYY” as a second parameter to the TO_CHAR() function to get the date in the specified format:

SELECT TO_CHAR(CURRENT_DATE, 'MONTH DD, YYYY');

This time, the TO_CHAR() function will return the complete name of the month in capital letters. Next comes the current day of the month and, finally, the year in YYYY format:

The output authenticates the working of the TO_CHAR() function.

Example #3: How to Get a Date in the “mon dd, yy” Format?

Specifying the “mon dd, yy” format as the second parameter in the TO_CHAR() function will return the abbreviated name of the month in small letters. Then the day and year in “dd” and “yy” format, respectively:

SELECT TO_CHAR(CURRENT_DATE, 'mon dd, yy');

This is how the TO_CHAR() function works in PostgreSQL.

Example #4: How to Get the Current Date in the Day Month Year Format?

We utilized the CURRENT_DATE function in the above examples to retrieve the prevailing date. However, we can use the NOW() function instead of the CURRENT_DATE function to get the current date as shown in the below snippet:

SELECT TO_CHAR(NOW()::DATE, 'dd Month, yyyy');

This is how you can use the NOW() and CURRENT_DATE functions with the collaboration of the TO_CHAR() function to get the current date in a specific format.

Example #5: How to Format Table’s Data Into a User-Specified Format?

We have a table named bike_details whose records are shown in the below snippet:

SELECT * FROM bike_details;

Let’s apply the TO_CHAR() function on the bike_launch_date column to get the specified dates into “dd Month, yyyy” format:

SELECT TO_CHAR(bike_launch_date::DATE, 'dd Month, yyyy')
FROM bike_details;

This is how you can format a date in Postgres using the TO_CHAR() function.

Conclusion

To get a date into a specific format, the TO_CHAR() function is used. In PostgreSQL, the NOW() and CURRENT_DATE functions are used with the collaboration of the TO_CHAR() function to get the current date in a specific format. The NOW() and CURRENT_DATE functions return the date in YYYY-MM-DD format. However, to get the date in a specific format, the TO_CHAR() function is used. In this write-up, we go through multiple examples to explain the concept of the NOW(), CURRENT_DATE, and TO_CHAR() functions in a better way.