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 formatMM
: month in 2 digits formatDD
: 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