Dates and times

How do you convert a string to a date in PostgreSQL?

By Owen Middleton · Updated September 2026 · Examples run on PostgreSQL 17

If the string is in ISO format, cast it: '2026-03-14'::date. For any other format, use to_date() with a pattern that describes the string: to_date('25/12/2026', 'DD/MM/YYYY').

Use to_timestamp() the same way when the string has a time in it. Watch out for two different failures before you convert a whole column in SQL: one from the cast, one from to_date().

Every query here ran in PostgreSQL, and you can edit and run them yourself in the two editors below, plus two exercises at the end. See our other guides to SQL dates in PostgreSQL.

How do you cast a string to a date in PostgreSQL?

Add ::date to it, or use CAST(... AS date). Use either; they are the same operation. Run to_date() on an ISO string and you get the same answer:

SELECT '2026-03-14'::date AS cast_operator,
       CAST('2026-03-14' AS date) AS cast_function,
       to_date('2026-03-14', 'YYYY-MM-DD') AS to_date;

cast_operator	cast_function	to_date
2026-03-14	2026-03-14	2026-03-14

You can cast more than the ISO format. Cast all five of these and you get the date you expect:

SELECT input,
       input::date AS as_date
FROM (VALUES ('2026-03-14'),
             ('20260314'),
             ('2026-3-4'),
             ('Mar 14 2026'),
             ('14 March 2026')) AS t(input);

input	as_date
2026-03-14	2026-03-14
20260314	2026-03-14
2026-3-4	2026-03-04
Mar 14 2026	2026-03-14
14 March 2026	2026-03-14

five spellings, one date each

Why does '25/12/2026' fail to convert?

Because the field order comes from your server's DateStyle setting, and on the default MDY the first number is the month. There is no month 25:

SELECT '25/12/2026'::date;

PostgreSQL responds date/time field value out of range: "25/12/2026"

Slashes are not the problem. Write the same day-first date with dashes and PostgreSQL raises the same error. Put the day first and you are in the same trouble, whatever you put between the numbers:

SELECT '25-12-2026'::date;

PostgreSQL responds date/time field value out of range: "25-12-2026"

Run it yourself: the failing cast is in the editor below. Try the dashed spelling in it too.

SELECT '25/12/2026'::date;

Those errors are the lucky case. When the day is 12 or less, PostgreSQL misreads the date without a word, in either spelling: ask for the third of April and you get the fourth of March.

SELECT '03/04/2026'::date AS with_slashes,
       '03-04-2026'::date AS with_dashes;

with_slashes	with_dashes
2026-03-04	2026-03-04

slashes or dashes, both read as March 4

PostgreSQL takes the order from the DateStyle setting. In this sandbox that setting is ISO, MDY, month first. The built-in default is month first too, but the PostgreSQL documentation says initdb writes a setting that matches the chosen lc_time locale, so on a server set up with a day-first locale you start at DMY instead. Run this on yours to check:

SHOW DateStyle;

DateStyle
ISO, MDY

That is what makes a column of day-first dates dangerous under MDY. Up to the 12th of the month PostgreSQL takes the string without a word and reads your day as the month. From the 13th on you get the error above. Changing DateStyle for the session is one fix. Saying what the string looks like, with to_date(), is a fix that does not depend on anyone’s settings.

Try it: change '03/04/2026'::date in the query below to to_date('03/04/2026', 'DD/MM/YYYY') and run it. You get April 3 instead of March 4.

SELECT '03/04/2026'::date AS with_slashes,
       '03-04-2026'::date AS with_dashes;

How do you convert DD/MM/YYYY with to_date?

Give to_date() the string and a pattern in the same shape. DD is the day, MM the month number and YYYY the four-digit year:

SELECT to_date('25/12/2026', 'DD/MM/YYYY') AS christmas,
       to_date('03/04/2026', 'DD/MM/YYYY') AS third_of_april;

christmas	third_of_april
2026-12-25	2026-04-03

Use month names the same way: Month for a full name, Mon for a three-letter one:

SELECT to_date('March 14, 2026', 'Month DD, YYYY') AS from_month_name,
       to_date('14 Mar 2026', 'DD Mon YYYY') AS from_short_name;

from_month_name	from_short_name
2026-03-14	2026-03-14

To convert a whole text column in place, put the same call in the USING clause. For a table called imports with the strings in a text column called order_date, that is ALTER TABLE imports ALTER COLUMN order_date TYPE date USING to_date(order_date, 'DD/MM/YYYY'). With USING order_date::date instead, you fail at the first day of 13 or more.

An ALTER COLUMN ... TYPE rewrites the column, and the strings it was holding are not kept anywhere, so afterwards there is nothing to compare against and nothing to retry from. Run it inside a BEGIN you can ROLLBACK, or add a new date column and fill it from the text one, which leaves both on the table until you have checked them against each other. Unlike every result above, the two ALTER TABLE statements here were not run: this site has no text-typed date column to run them against, so read them as the reason to check before you convert.

An error during the conversion is the good outcome. On a column where no day passes the 12th the ::date form raises nothing at all: under MDY PostgreSQL reads every one of those strings month-first and writes the swapped date into the column. Convert '03/04/2026' and '11/02/2026' that way and you are left with March 4 and November 2, not April 3 and February 11. A clean run is not evidence the data was fine.

Does to_date check the whole string?

No. PostgreSQL reads your pattern field by field off the front of the string, and for a number field keeps taking digits until it reaches a character that is not a digit. Whatever is left once the pattern runs out is dropped, so here you lose the trailing word and get no error:

SELECT to_date('2026-03-14 garbage', 'YYYY-MM-DD') AS converted;

converted
2026-03-14

the extra text was dropped, not rejected

Cast the same string and PostgreSQL refuses it:

SELECT '2026-03-14 garbage'::date;

PostgreSQL responds invalid input syntax for type date: "2026-03-14 garbage"

Leftover digits are the case that does not get dropped, because PostgreSQL keeps reading them into the field it is on. Pad the day out with a leading zero and PostgreSQL reads all three digits as one day number:

SELECT to_date('2026-03-011', 'YYYY-MM-DD') AS day_eleven,
       to_date('2026-03-031', 'YYYY-MM-DD') AS day_thirty_one;

day_eleven	day_thirty_one
2026-03-11	2026-03-31

three digits read as one day, twice

So a stray digit on the end is not ignored. Add one to the 14th and PostgreSQL reads the day as 149, which is past the end of every month:

SELECT to_date('2026-03-149', 'YYYY-MM-DD');

PostgreSQL responds date/time field value out of range: "2026-03-149"

Give PostgreSQL a pattern in the wrong shape and the error you get is misleading. Read a year-first string with a day-first pattern and PostgreSQL reports a value out of range rather than a wrong format:

SELECT to_date('2026-12-25', 'DD/MM/YYYY');

PostgreSQL responds date/time field value out of range: "2026-12-25"

Both routes refuse a month past 12 and a day past the end of the month you named. Ask for February 31 and you fail, with to_date() and with a cast:

SELECT to_date('2026-02-31', 'YYYY-MM-DD');

PostgreSQL responds date/time field value out of range: "2026-02-31"

SELECT '2026-02-31'::date;

PostgreSQL responds date/time field value out of range: "2026-02-31"

The bottom of the range is where the two routes part, and this is the half to know before you convert a column. Inside to_date() PostgreSQL takes a zero month and a zero day and reads each one as 1, and reads the year 0000 as 1 BC. You get a date back from all three, and no complaint:

SELECT to_date('2026-00-15', 'YYYY-MM-DD') AS zero_month,
       to_date('2026-03-00', 'YYYY-MM-DD') AS zero_day,
       to_date('0000-00-00', 'YYYY-MM-DD') AS zero_date;

zero_month	zero_day	zero_date
2026-01-15	2026-03-01	0001-01-01 BC

a zero month, a zero day, and a zero date

Cast that last string and PostgreSQL raises the error instead:

SELECT '0000-00-00'::date;

PostgreSQL responds date/time field value out of range: "0000-00-00"

0000-00-00 is the value MySQL documents as a permitted dummy date, so you can meet it in a text column imported from MySQL. Run the USING to_date(...) migration above over a column holding one and PostgreSQL writes 0001-01-01 BC into the row without a word. Count those rows before you convert.

How do you convert a string with a time in it?

Use to_timestamp() with the time added to the pattern: HH24 for the hour on a 24-hour clock and MI for minutes. You can also cast an ISO string with ::timestamp:

SELECT to_timestamp('2026-03-14 16:05', 'YYYY-MM-DD HH24:MI') AS with_zone,
       '2026-03-14 16:05'::timestamp AS without_zone;

with_zone	without_zone
2026-03-14 16:05:00+00	2026-03-14 16:05:00

the same moment, with and without a time zone

You see +00 on one and not the other because they are different types. Run to_timestamp() and you always get a timestamp with a time zone, and PostgreSQL reads the string in the session’s time zone, which is UTC in this sandbox:

SELECT pg_typeof(to_timestamp('2026-03-14 16:05', 'YYYY-MM-DD HH24:MI')) AS to_timestamp_returns;

to_timestamp_returns
timestamp with time zone

What happens if you use STR_TO_DATE or CONVERT?

Neither call works here, and not for the same reason. PostgreSQL has no STR_TO_DATE at all: no function of that name exists, whatever arguments you hand it. Write MySQL’s call here and PostgreSQL says so:

SELECT STR_TO_DATE('14/03/2026', '%d/%m/%Y');

PostgreSQL responds function str_to_date(unknown, unknown) does not exist

convert is the other thing: PostgreSQL does ship one, and it has nothing to do with dates. Its only signature is convert(bytea, name, name), and the PostgreSQL documentation describes it as converting a binary string representing text from one character encoding to another.

SQL Server’s CONVERT(date, ...) never reaches it. date sits in an argument position as a bare word, so PostgreSQL resolves it as a column reference before it looks for a function at all, and stops there. That is why the error names a column and no function:

SELECT CONVERT(date, '2026-03-14');

PostgreSQL responds column "date" does not exist

Replace STR_TO_DATE(s, '%d/%m/%Y') with to_date(s, 'DD/MM/YYYY'), and CONVERT(date, s) with s::date. MySQL documents STR_TO_DATE(str, format) as taking a string and a format string, and Microsoft documents CONVERT(data_type, expression, style) as converting an expression of one data type to another.

To go from a date back to text, use the same pattern letters: see our guide to to_char().

Practice: convert imported strings

You are doing a type conversion when you cast a string to a date, which is the lesson in type casting, and you learn what the resulting types can hold in the date and time types, part of our free SQL course. No account, nothing to install, nothing to pay.