Postgresql date to string

In this tutorial, we will learn about “Postgresql date to string” and cover the following topics.

• Postgresql date to string format
• Postgresql date to string yyymmdd
• Postgresql date string to timestamp
• Postgresql curren_date to string

Postgresql date to string format

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

syntax:

CAST(date AS datatype)

Where,

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

Let’s convert the date to string format.

SELECT CAST(current_date AS TEXT);

In the above code, within the CAST function, we are retrieving the current date using the function current_date and converting this current date to a string using the data type TEXT.

The output of the above code is given below.

Read: Postgresql current_timestamp

Postgresql date to string yyymmdd

In Postgresql, dates can be converted in specific string formats like yyymmdd, where yyyy for a year, mm for a month, and dd for the date.

For that conversion, we will use the to_char function to format the date as a string.

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 for a month and dd is the date.

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

The output of the above code is given below.

Read: PostgreSQL list users

Postgresql date string to timestamp

In Postgresql, we can convert the date string to timestamp using the CAST function.

If you don’t know about the CAST function, please refer to our sub-section of this tutorial “Postgresql date to string format”, which is given above.

Let’s convert the date string to timestamp.

SELECT cast('2021-01-12 15:50:34' AS timestamp);

In the above code, we are converting the string date ‘2021-01-12 15:50:34’ to the timestamp, that we provided in the cast function.

The output of the above code is given below.

Read: Postgresql difference between two timestamps

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

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

So, in this tutorial, we have learned about “Postgresql date to string” and covered the following topics.

• Postgresql date to string format
• Postgresql date to string yyymmdd
• Postgresql date string to timestamp
• Postgresql curren_date to string