Summary: in this tutorial, you will learn how to use the PostgreSQL TO_DATE() function to convert a string to a date.

Introduction to the PostgreSQL TO_DATE() function

The TO_DATE() function converts a string literal to a date value.

Here’s the basic syntax of the TO_DATE() function:

TO_DATE(text,format);

The TO_DATE() function accepts two string arguments.

  • text: is an input string that you want to convert to a date.
  • format: specifies the format of the input string.

The TO_DATE() function returns a DATE value.

See the following example:

SELECT TO_DATE('20170103','YYYYMMDD');

Output:

TO_DATE
------------
 2017-01-03

In this example, the string 20170103 is converted into a date based on the input format YYYYMMDD.

  • YYYY: year in four digits format.
  • MM: month in two digits format.
  • DD: day in two digits format.

As a result, the function returns January 3rd 2017.

The following table illustrates the template patterns for formatting date values:

PatternDescription
Y,YYYyear in 4 digits with comma
YYYYyear in 4 digits
YYYlast 2 digits of the year
YYThe last digit of the year
YThe last 3 digits of ISO 8601 week-numbering year
IYYYISO 8601 week-numbering year (4 or more digits)
IYYThe last 2 digits of ISO 8601 week-numbering year
IYThe last digit of ISO 8601 week-numbering year
IAbbreviated capitalized month name e.g., Jan, Feb,  etc.
BC, bc, AD or adEra indicator without periods
B.C., b.c., A.D. ora.d.Era indicator with periods
MONTHEnglish month name in uppercase
MonthFull capitalized English month name
monthFull lowercase English month name
MONAbbreviated uppercase month name e.g., JAN, FEB, etc.
MonWeek number of the year (1-53) (the first week starts on the first day of the year)
monAbbreviated lowercase month name e.g., Jan, Feb, etc.
MMmonth number from 01 to 12
DAYFull uppercase day name
DayFull capitalized day name
dayFull lowercase day name
DYAbbreviated uppercase day name
DyAbbreviated capitalized day name
dyAbbreviated lowercase day name
DDDDay of year (001-366)
IDDDDay of ISO 8601 week-numbering year (001-371; day 1 of the year is Monday of the first ISO week)
DDDay of month (01-31)
DDay of the week, Sunday (1) to Saturday (7)
IDISO 8601 day of the week, Monday (1) to Sunday (7)
WWeek of month (1-5) (the first week starts on the first day of the month)
WWCentury e.g., 21, 22, etc.
IWWeek number of ISO 8601 week-numbering year (01-53; the first Thursday of the year is in week 1)
CCCentury e.g, 21, 22, etc.
JJulian Day (integer days since November 24, 4714 BC at midnight UTC)
RMMonth in uppercase Roman numerals (I-XII; I=January)
rmMonth in lowercase Roman numerals (i-xii; i=January)

PostgreSQL TO_DATE function examples

The following statement converts the string 10 Feb 2017 into a date value:

SELECT TO_DATE('10 Feb 2017', 'DD Mon YYYY');

The output is:

TO_DATE
------------
 2017-02-10
(1 row)

Suppose you want to convert the string 2017 Feb 10 to a date value, you can apply the pattern YYYY Mon DD as follows:

SELECT TO_DATE('2017 Feb 20','YYYY Mon DD');

The function returns the following output:

TO_DATE
------------
 2017-02-20
(1 row)

PostgreSQL TO_DATE() function gotchas

If you pass an invalid date string, the TO_DATE() function will try to convert it to a valid date and issue an error if it cannot. For example:

SELECT TO_DATE('2017/02/30', 'YYYY/MM/DD');

PostgreSQL issued the following error:

ERROR:  date/time field value out of range: "2017/02/30"
LINE 1: SELECT '2017/02/30'::date;

Summary

  • Use the PostgreSQL TO_DATE() function to convert a string literal to a date value.