PostgreSQL date to string

In this comprehensive article, I’ll explain the various methods for converting PostgreSQL date values to formatted strings with practical examples. By the end, you’ll have a clear picture of this.

PostgreSQL date to string

Let us discuss all the approaches individually.

Approach-1 Using the CAST function

In PostgreSQL, dates are converted into strings using the CAST function.

Syntax:

CAST(date AS datatype)

Where,

• Date: We want to convert the date into a string, which can be any literal or an expression that evaluates to a date value.
• datatype: It is the data type and can take values for strings like VARCHAR or TEXT.
• CAST() function returns the date, which has a string data type.

Let’s convert the date to a string format.

SELECT CAST(current_date AS TEXT);

In the above code, within the CAST function, we retrieve the current date using the function current_date and convert it to a string using the data type TEXT.

After executing the above query, we got the expected output as shown in the screenshot below.

Read: Postgresql current_timestamp

Approach-2: Using the to_char function

In PostgreSQL, dates can be converted to specific string formats like yyymmdd, where yyyy is a year, mm is a month, and dd is the day.

We will use the to_char function to format the date as a string for that conversion.

Syntax:

TO_CHAR(date_value, string_format);

Where,

• date_value: It can be the date or any other value.
• string_format: pattern for showing the date string like yyyymmdd, ddmmyyy, etc.

Let’s convert the date into a string.

SELECT to_char(current_date,'yyymmdd');

From the above code, we are converting the current date to a specific string date format like yyymmdd, where yyy is the last three digits of the year, mm is the month, and dd is the date.

In the place of current_date, we can also provide our own date or a custom date.

After executing the above query, I got the expected output as shown in the below screenshot.

Read: PostgreSQL date string to timestamp

Read: Postgresql difference between two timestamps

Approach-3 Combining DATE_PART with String Functions

We can also combine the DATE_PART with String Functions for this purpose.

SELECT 
    LPAD(DATE_PART('month', sale_date)::TEXT, 2, '0') || '-' ||
    LPAD(DATE_PART('day', sale_date)::TEXT, 2, '0') || '-' ||
    DATE_PART('year', sale_date)::TEXT AS sales_date
FROM 
    Sales;

After executing the above query, I got the expected output as shown in the below screenshot.

Approach-4 Converting curren_date to string

In Postgresql, current_date is converted into a string using the CAST function.

Let’s convert the current_date to string.

SELECT cast(current_date AS TEXT);

In the above code, we are accessing the current date using the current_date function within the CAST function, and converting the current date to a string by providing the data type as TEXT.

The output of the above code is given below.

So, in this tutorial, we have learned how to convert date to string in PostgreSQL.

You may also like to read the following tutorials.

• Postgresql Format
• PostgreSQL Update
• Postgresql Add Foreign Key
• Postgresql Having Clause
• Postgresql Sum
• PostgreSQL list databases
• PostgreSQL Data Types
• PostgreSQL Date Add
• Postgresql row_number
• Postgresql auto increment