How to Convert String to Date in PostgreSQL

How to Convert String to Date in PostgreSQL

PostgreSQL converts text to a DATE value with the TO_DATE(format_text, format_mask) function or a direct cast (::date). Choose TO_DATE when the input format is not ISO-8601.

Which function converts string to date?

Call TO_DATE(text, text) when the source string is not in the default YYYY-MM-DD format. The first argument is the literal, the second argument is the format mask that tells PostgreSQL how to parse it.

When can I use a simple cast?

Use CAST(text AS DATE) or text::date if the string already follows ISO-8601 (e.g., 2024-01-15). PostgreSQL parses this without an explicit mask.

What is the exact TO_DATE syntax?

The function signature is TO_DATE(string text, format text) RETURNS date. The format mask uses the same template patterns as TO_CHAR (e.g., YYYY, MM, DD, HH24).

How do I handle time zones?

TO_DATE ignores time components. If you need both date and time, switch to TO_TIMESTAMP or cast to timestamp with time zone.

Practical examples

Convert US style date

SELECT TO_DATE('03/15/2024', 'MM/DD/YYYY'); → 2024-03-15

Convert verbose month

SELECT TO_DATE('15-Mar-2024', 'DD-Mon-YYYY'); → 2024-03-15

ISO cast

SELECT '2024-03-15'::date; → 2024-03-15

Best practices for reliable parsing

Always supply an explicit format mask when the source string is not ISO-8601. Validate input with a regular expression in application code before sending it to the database.

Why does TO_DATE return 2004 instead of 04?

PostgreSQL treats a two-digit year as 2000-2099. Provide the full four-digit year or use to_date('04', 'YY') intentionally.

How can I bulk convert a column?

Use UPDATE with TO_DATE and create a new date column or replace the old text column: UPDATE orders SET order_date = TO_DATE(order_date_txt, 'DD/MM/YYYY');

Does casting work inside COPY?

Yes. Define the destination column as DATE. PostgreSQL will attempt to cast each field during COPY. Provide the correct DATESTYLE or preprocess data.