How to Convert String to Date Format in MySQL
This article will guide you and take you through different functions to convert a string type value that acts as DATE to a DATE type value. We will also practice with some example code using STR_TO_DATE(), CONVERT(), and CAST() functions in MySQL.
NOTE: We are using MySQL V8.0.27. You may get the newer one from here (if available)
Convert String to Date Format in MySQL
We will learn the following methods to convert string to DATE format in MySQL.
MySQL STR_TO_DATE() Function
This function converts the str (string) into DATE value. The format of DATE will be according to your string format. For example, you want to convert MM/DD/YYYY from string type to DATE type, then the DATE will also be in MM/DD/YYYY format.
STR_TO_DATE() function can return DATE, DATETIME, or TIME value based on the input as well as string format. See the examples given below and compare them with the output.
SELECT STR_TO_DATE('2021-08-23', '%Y-%m-%d');
OUTPUT:
What if we change the format? Let’s replace the dash with a comma.
SELECT STR_TO_DATE('2021-08-23', '%Y,%m,%d');
OUTPUT:
See the output given above, it returned NULL. Let’s change the format only from YYYY-MM-DD to DD-MM-YYYY.
SELECT STR_TO_DATE('2021-08-23', '%d-%m-%Y');
OUTPUT:
See, it returned NULL again. That means you must have the same DATE format as you have in your input string. What if we have TIME also? Let’s run the following command.
SELECT STR_TO_DATE('2021-08-23 10:12:23', '%Y-%m-%d %T');
OUTPUT:
STR_TO_DATE() function will ignore the text and output the DATE only. See the following example.
SELECT STR_TO_DATE('2021-08-23 some other string', '%Y-%m-%d') as Date;
OUTPUT:
We can also extract the year. If you extract the year, it will set the day and month to zero. See the following code and output.
SELECT STR_TO_DATE('2021-08-23', '%Y') as Year; #get year
OUTPUT:
MySQL CONVERT() Function
The Convert function can convert the given value to a specified character set or datatype. You can see the details about data type here.
SELECT CONVERT("2021-08-19", DATE); #convert to DATE
SELECT CONVERT("2021-08-19", DATETIME); #convert to DATETIME
SELECT CONVERT("2021-08-19", TIME); #convert to TIME
OUTPUT:
MySQL CAST() Function
This function converts the input of any type (it can be string, integer, or something else) to any specified datatype. You can see the parameter values here in detail.
SELECT CAST("2021-08-29" AS DATE) as date; #convert to date type
SELECT CAST("2021-08-29 11:12:45" AS DATETIME) as datetime; #convert to datetime type
SELECT CAST("11:12:45" AS TIME) as time; #convert to time type
OUTPUT:
Conclusions
We concluded that there can be multiple ways of converting a string value to DATE, TIME, and DATETIME as per the project requirements. We learned about STR_TO_DATE, CONVERT, and CAST functions for conversion purposes.
