Summary: this tutorial introduces you to the PostgreSQL TO_DATE() function that helps you 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. The following illustrates the syntax of the TO_DATE() function:
TO_DATE(text,format);Code language: SQL (Structured Query Language) (sql)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');Code language: SQL (Structured Query Language) (sql)The output shows:
TO_DATE
------------
2017-01-03
Code language: SQL (Structured Query Language) (sql)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');
Code language: SQL (Structured Query Language) (sql)The output is:
TO_DATE
------------
2017-02-10
(1 row)
Code language: SQL (Structured Query Language) (sql)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');
Code language: SQL (Structured Query Language) (sql)The function returns the following output:
TO_DATE
------------
2017-02-20
(1 row)
Code language: SQL (Structured Query Language) (sql)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');
Code language: SQL (Structured Query Language) (sql)PostgreSQL issued the following error:
ERROR: date/time field value out of range: "2017/02/30"
LINE 1: SELECT '2017/02/30'::date;Code language: PHP (php)In this tutorial, you have learned how to use the PostgreSQL TO_DATE() function to convert a string literal to a date value.
