PostgreSQL Tutorial: TO_DATE Function: Convert String to Date

September 19, 2023

Summary: This tutorial introduces you to the PostgreSQL TO_DATE() function that helps you convert a string to a date.

Table of Contents

Introduction to the PostgreSQL TO_DATE function

The TO_DATE() function converts a string literal to a date value. The following illustrates the syntax of the TO_DATE() function:

TO_DATE(text,format);

The TO_DATE() function accepts two string arguments. The first argument is the string that you want to convert to a date. The second one is the input format. The TO_DATE() function returns a date value.

See the following example:

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

The output shows:

  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 2 digits format
  • DD: day in 2 digits format

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

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

Pattern Description
Y,YYY year in 4 digits with comma
YYYY year in 4 digits
YYY last 3 digits of year
YY last 2 digits of year
Y The last digit of year
IYYY ISO 8601 week-numbering year (4 or more digits)
IYY Last 3 digits of ISO 8601 week-numbering year
IY Last 2 digits of ISO 8601 week-numbering year
I Last digit of ISO 8601 week-numbering year
BC, bc, AD or ad Era indicator without periods
B.C., b.c., A.D. ora.d. Era indicator with periods
MONTH English month name in uppercase
Month Full capitalized English month name
month Full lowercase English month name
MON Abbreviated uppercase month name e.g., JAN, FEB, etc.
Mon Abbreviated capitalized month name e.g, Jan, Feb, etc.
mon Abbreviated lowercase month name e.g., jan, feb, etc.
MM month number from 01 to 12
DAY Full uppercase day name
Day Full capitalized day name
day Full lowercase day name
DY Abbreviated uppercase day name
Dy Abbreviated capitalized day name
dy Abbreviated lowercase day name
DDD Day of year (001-366)
IDDD Day of ISO 8601 week-numbering year (001-371; day 1 of the year is Monday of the first ISO week)
DD Day of month (01-31)
D Day of the week, Sunday (1) to Saturday (7)
ID ISO 8601 day of the week, Monday (1) to Sunday (7)
W Week of month (1-5) (the first week starts on the first day of the month)
WW Week number of year (1-53) (the first week starts on the first day of the year)
IW Week number of ISO 8601 week-numbering year (01-53; the first Thursday of the year is in week 1)
CC Century e.g, 21, 22, etc.
J Julian Day (integer days since November 24, 4714 BC at midnight UTC)
RM Month in uppercase Roman numerals (I-XII; I=January)
rm Month in lowercase Roman numerals (i-xii; i=January)

PostgreSQL TO_DATE 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 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;

In this tutorial, you have learned how to use the PostgreSQL TO_DATE() function to convert a string literal to a date value.

See more

PostgreSQL Tutorial: Date Functions

PostgreSQL Documentation: Data Type Formatting Functions

to_timestamp(), to_char(), to_number()

Search

Categories

Tags