How to convert a string to a date in SQL

Problem

You have a column in your database table that contains dates stored as strings, and you need to convert these strings into date data type.

Sample Data

Let's consider a table Events with some sample data as shown below:

In this table, the DateString column is stored as a string instead of a date data type.

MySQL Solution

SELECT EventID, STR_TO_DATE(DateString, '%Y-%m-%d') AS NewDate
FROM Events;

Explanation (MySQL)

In MySQL, the STR_TO_DATE() function is used to convert a string into a date. The function takes two arguments: the string to be converted, and the format of the date in the string. In our case, the format is %Y-%m-%d where %Y is a four-digit year, %m is the month in two-digit format, and %d is the day in two-digit format.

This query will return a result where DateString is converted to a date data type.

PostgreSQL Solution

SELECT EventID, TO_DATE(DateString, 'YYYY-MM-DD') AS NewDate
FROM Events;

Explanation (PostgreSQL)

In PostgreSQL, the TO_DATE() function is used to convert a string into a date. The function takes two arguments: the string to be converted, and the format of the date in the string. In our case, the format is 'YYYY-MM-DD' where 'YYYY' is a four-digit year, 'MM' is the month in two-digit format, and 'DD' is the day in two-digit format.

This query will return a result where DateString is converted to a date data type.