PostgreSQL To_Char Date Format

In this PostgreSQL tutorial, I will show you how to use PostgreSQL TO_CHAR format date, which means you will understand how to use the TO_CHAR() function to format date in a meaningful way.

I will also explain to you the importance of the PostgreSQL TO_CHAR() function and how it can be helpful in presenting the information in an easy-to-understand format.

You will learn practical examples where I will show you how to use TO_CHAR() on a column of type date in the table and also how to use the TO_CHAR() function with INSERT statement.

PostgreSQL To_Char Date Format

TO_CHAR function in PostgreSQL converts the date, numbers and timestamp value into strings to represent the information or data in a meaningful and understandable format. So how TO_CHAR function is very helpful.

• Think about a global company which has many restaurants in the countries, and they have an app to track the sales. The people in the USA like to see dates in a format as ‘month-day-year’ and the people in the UK like to see dates in the format ‘day-month-year’.
• So to keep the date format according to country-specific means as people like to see the date, the TO_CHAR() function can be used to present the date in a different format.
• Basically, it converts the date in the format according to the user or the place so everyone sees it in their preferred way.
• Let’s take one more example and understand how the TO_CHAR() function can be helpful. When two computers talk to each other and they belong to different regions, The TO_CHAR() function can take data and numbers and present them in the format so that both can understand, It acts as a translator.

Now you know the importance of the TO_CHAR() function, let’s see how you can use it in PostgreSQL to format the date.

The syntax is given below.

TO_CHAR(exp_value, format)

• TO_CHAR: It is the function itself to format values.
• exp_value: It is an expression that you pass to the TO_CHAR() function to convert it into a specific format.
• format: It is the format and defines how you want to format the expression.

The output of the TO_CHAR() function is the text or string value of the provided expression. Let’s take an example and understand you to use the TO_CHAR() function to format the date.

Here in this tutorial, we will only focus on the date and time expressions.

Using PostgreSQL To_Char to Format Date

Before beginning, you need to know some common date and time patterns or formats that you can use with the TO_CHAR() function to format the given date and time.

Format Or Pattern	Meaning
D	It is a day of the week from 1 to 7.
DD	It is a day of the month from 01 to 31.
DY	It is a short day name such as Tue, Mon and Sun etc.
DAY	It is the full name of the day such as Monday, Sunday etc.
MM	It represents the month from 01 to 12.
M	It is a short name of the month such as Jan, Feb etc.
MON	It is the full name of the month such as January, February etc.
YYYY	It represents the 4-digit year such as 2023
YY	It represents the 2-digit year such as 23
HH24	It is the hour of the day from 00 to 24.
MI	It is the minutes from 00 to 59.
SS	It is the seconds from 00 to 59.

Let’s start with a simple example of how to use PostgreSQL to format dates using TO_CHAR() function.

Format the date 2023-09-07 using the below query.

SELECT TO_CHAR('2023-09-07'::DATE, 'DD-MON-YYYY');

Look you how the date ‘2023-09-07’ converted to the meaningful date format as 07-SEP-2023 in the above output. The TO_CHAR() takes the expression as ”2023-09-07′::DATE’, and the format as DD-MON-YYYY and converts it into the specified format.

The string ‘2023-09-07’ is converted to date value using type cast operator ::DATE. If you want to know how to cast string or number into different data types in PostgreSQL, then refer to this tutorial Postgresql cast int + Examples

If you want to present the given date in a more understandable format, maybe you want to include the full-day name with the date ‘2023-09-07’. Use the below query.

SELECT TO_CHAR('2023-09-07'::DATE, 'Day, DD Month YYYY');

In the above query look at the format part ‘Day, DD Month YYYY’, where the pattern ‘Day’ is used to get the full day name from the given date as in the output you can see the Thursday, 07 September 2023, this is more meaningful than the date in the previous example.

Using PostgreSQL To_Char to Format Date in Table

Till now you know how to use the TO_CHAR() function with the SELECT statement to format a given date but here you are going to understand how to use the TO_CHAR() function with actual tables to communicate information in a meaningful way.

Suppose you work at an e-commerce platform and you have been given a task to generate monthly sales reports. Company management wants you to present the sales reports containing the date in a detailed format means it should contain the day of the week too.

The sales table is given below.

Use the below query to generate the sales reports with detailed date format.

SELECT 
    TO_CHAR(sale_date, 'Day, DD-MON-YYYY') as FormattedDate,
    SUM(sale_amount) as TotalSales
FROM 
    sales
GROUP BY 
    sale_date
ORDER BY 
    sale_date DESC;

Look at the above output and how the date is formatted in a detailed way containing the day of the week for a monthly total sale of each product such as total sales on Tuesday, 24-JAN-2023 is 310.45 dollars.

The query part TO_CHAR(sale_date, ‘Day, DD-MON-YYYY’) as FormattedDate converts the date of column sale_date in the format ‘Day, DD-MON-YYYY’, where the format ‘Day’ is the week name and ‘DD-MON-YYYY’ is the actual date.

Take one more example, where you have been given the task to generate an invoice number that should contain the year and month of the invoice creation, followed by a sequence number. There is a table called invoices which contains the invoices of all the customers.

Suppose whenever a customer buys anything, the invoice is generated and stored in the invoice table. So use the TO_CHAR() function with INSERT statement using the below query.

INSERT INTO invoices (invoice_number, customer_id, amount) VALUES
(TO_CHAR(NOW(), 'YYMM') || '_002', 124, 300.00);

SELECT * FROM invoices;

The above insert statement created an invoice with the number 2309_002 of the customer with ID 124 and an amount equal to 300.00.

• In the query part (TO_CHAR(NOW(), ‘YYMM’) || ‘_002’), the TO_CHAR() function takes the current date using the NOW() function and formats it using the pattern ‘YYMM’ which outputs the date as 2309 where 23 and 09 is year and month respectively.
• And then uses the pipe || operator to append the string value ‘_002’ which sequence number to that date. Finally, it generates the invoice number as 2309_002 which contains the year and month of the current date followed by the sequence number.

Now you know how to format the date in PostgreSQL using the TO_CHAR() function. You can use different formats with the date to achieve the desired date format.

Conclusion

In this PostgreSQL tutorial, you learned how to use the PostgreSQL TO_CHAR() function to format the date. Also, you learned how to use the TO_CHAR() function with a SELECT statement to format the date and how to use it on the column of type date. Finally, you learned how to format the date value in the INSERT statement to insert meaningful information in the table.

You may also read:

• How to Find PostgreSQL DateDiff
• PostgreSQL DATE Functions with Examples
• PostgreSQL Date Add + Add days to date in PostgreSQL Examples