In PostgreSQL, you can use the to_date() function to convert a string to a date.

Syntax

The syntax goes like this:

to_date(text, text)

Where the first argument is a string representation of the date, and the second argument defines the template that the first argument uses.

Example

Here’s a basic example to demonstrate.

SELECT to_date('10 Feb 2010', 'DD Mon YYYY');

Result:

2010-02-10

Templates

The second argument provides the template that the first argument is provided in. This is sometimes referred to as a “format string”.

The template consists of one or more template patterns, and optionally, one or more template pattern modifiers.

Template Patterns

Using the previous example, DD is a template pattern and Mon is a template pattern. YYYY is also a template pattern. These were all combined to form the template.

You can move each template pattern around as required. And you can use different template patterns altogether.

Here are some more examples that uses the same date, but in different formats (and therefore, using different templates).

\x
SELECT 
  to_date('10 Feb 2010', 'DD Mon YYYY') AS "DD Mon YYYY",
  to_date('Feb 10 2010', 'Mon DD YYYY') AS "Mon DD YYYY",
  to_date('10/02/2010', 'DD/MM/YYYY') AS "DD/MM/YYYY",
  to_date('02/10/2010', 'MM/DD/YYYY') AS "MM/DD/YYYY",
  to_date('02-10-2010', 'MM-DD-YYYY') AS "MM-DD-YYYY",  
  to_date('02102010', 'MMDDYYYY') AS "MMDDYYYY",
  to_date('02-10-10', 'MM-DD-YY') AS "MM-DD-YY";

Result (using vertical output):

DD Mon YYYY | 2010-02-10
Mon DD YYYY | 2010-02-10
DD/MM/YYYY  | 2010-02-10
MM/DD/YYYY  | 2010-02-10
MM-DD-YYYY  | 2010-02-10
MMDDYYYY    | 2010-02-10
MM-DD-YY    | 2010-02-10

So they all use the same date, but the date is provided in different formats. It’s the template’s job to explicitly specify to Postgres what format the date has been provided in.

As you can probably imagine, if we didn’t provide the template, some dates could be interpreted the wrong way, especially if the date uses a different locale for its format.

For example, 01/03/2010 could be interpreted as the first of March or the third of January, depending on the locale.

Template Pattern Modifiers

Sometimes you might also need to add a template pattern modifier to the template.

For example, if your date uses an ordinal number suffix, you’ll need to ensure it’s interpreted as such.

SELECT 
  to_date('10th Feb 2010', 'DDth Mon YYYY') AS "10th Feb 2010",
  to_date('01st Feb 2010', 'DDth Mon YYYY') AS "01st Feb 2010",
  to_date('03rd Feb 2010', 'DDth Mon YYYY') AS "03rd Feb 2010";

Result (using vertical output):

10th Feb 2010 | 2010-02-10
01st Feb 2010 | 2010-02-01
03rd Feb 2010 | 2010-02-03

If I hadn’t used the template pattern modifier, I would have received an error when using those dates.

Here’s an example to illustrate.

SELECT to_date('10th Feb 2010', 'DD Mon YYYY');

Result:

ERROR: invalid value "th" for "Mon"
DETAIL: The given value did not match any of the allowed values for this field.

Full List of Template Patterns & Modifiers

Postgres includes plenty more template patterns and modifiers.

These can also be used when formatting date/time values (for example when using the to_char() function to convert a timestamp to a string).

See Template Patterns & Modifiers for Date/Time Formatting in PostgreSQL for a full list.