Problem:
You’d like to convert a string containing a date to the PostgreSQL date data type.
Example:
Let’s convert a date string to the date data type instead of its current text data type.
Solution:
Here’s Example 1 of how to use the TO_DATE() function. This is the query you would write:
SELECT TO_DATE('20230304', 'YYYYMMDD') AS new_date;And here’s the result of the query:
| new_date |
|---|
| 2023-03-04 |
Let’s look at Example 2 of the TO_DATE() function. Notice the slightly different date format:
SELECT TO_DATE('2023/06/07', 'YYYY/MM/DD') AS new_date;Here’s the result of the query:
| new_date |
|---|
| 2023-06-07 |
Discussion:
Use the function TO_DATE() to convert a text value containing a date to the date data type. This function takes two arguments:
- A date value. This can be a string (a text value) or a text column containing date information. In our example, we used the strings
'20230304'and'2023/06/07'. - The input date format. In our example, we used
'YYYYMMDD'and'YYYY/MM/DD'. Notice that the input format is a string.
The input format decides how PostgreSQL will process the characters in the string where the date is stored. The specifier YYYY as the first four characters indicates that these represent a 4-digit year. Next, MM represents a 2-digit month and DD a 2-digit day. You can find a list of all specifiers in the PostgreSQL documentation.
In Example 1, the string date '20230304' was converted to the date value of 2023-03-04 (a date data type). In Example 2, the string '2023/06/07' was converted to the date value of 2023-06-07. In the second example, we used the slash / between date parts to correctly convert from a string to the date data type.
