Convert String to Date Format in MySQL

  1. Convert String to Date Format in MySQL
  2. Conclusions

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:

convert string to date format in mysql - str to date output one

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:

convert string to date format in mysql - str to date error one

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:

convert string to date format in mysql - str to date error two

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:

convert string to date format in mysql - str to date output two

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:

convert string to date format in mysql - str to date output four

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:

convert string to date format in mysql - str to date output five

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:

convert string to date format in mysql - convert 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:

convert string to date format in mysql - cast 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.

Write for us
DelftStack articles are written by software geeks like you. If you also would like to contribute to DelftStack by writing paid articles, you can check the write for us page.