# How to Format a Date in PostgreSQL

> 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.

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.

---
[View this page online](https://www.commandprompt.com/education/how-to-format-a-date-in-postgresql/)