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:
1 | 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:
1 | SELECT to_date('20170103','YYYYMMDD'); |
The output shows:
1 2 3 | 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:
1 | SELECT to_date('10 Feb 2017', 'DD Mon YYYY'); |
The output is:
1 2 3 4 | 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:
1 | SELECT to_date('2017 Feb 20','YYYY Mon DD'); |
The function returns the following output:
1 2 3 4 | 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 which you may not wanted. See the following example:
1 | SELECT to_date('2017/02/30', 'YYYY/MM/DD'); |
It returns the following output:
1 2 3 4 | to_date ------------ 2017-03-02 (1 row) |
To convert this string to a date, you should cast the string literal to date explicitly:
1 | SELECT '2017/02/30'::date; |
PostgreSQL issued the following error:
1 2 | 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.