How to use MySQL cast function to convert string to date

In MySQL, there are some functions by default, which are used to get different results. The advantage of using functions is that MySQL functions help in enhancing its functionality. For example, we have a function in MySQL known as cast(), which is used to convert any expression to a specific type of datatype. For example, we can convert any string into the date using the function of the cast().

In this article, we will learn how the cast function is used to convert a string into the date and also other methods through which we can get the same conversion.

What is the cast string to date in MySQL

The cast function is used in MySQL to convert an expression into the specified datatype. We can use the string to convert into the format of date by using the cast function, which will take an expression of date in the form of string and will return the result in a date format that is YYYY-MM_DD. The restriction of this function is the range of date that should be in 1000-01-01 to 9999-12-31 else the function will generate the error. The general Syntax of using the cast function to convert the string expression into a date format is

SELECT CAST ([StringExpression] AS DATE);

In this syntax, the CAST is a function, [StringExpression] means the expression of string that is to be converted, AS represents the output in the mentioned data type, and DATE means to represent the string expression in date format.

To understand it more clearly, we will consider some examples, let’s say we want to convert a string ‘2021.12.13’ into the date format using the cast function.

SELECT CAST(‘2021.12.13’ AS DATE);

Convert 13,3,4 into the date format using the cast function.

SELECT CAST(‘13,3,4’ AS DATE);

To see what happens, when we give the string out of the range.

SELECT CAST(‘10000,20,35’ AS DATE);

It gives the NULL value as the output instead of the date output because the value is beyond the range of the function.

How to use the STR_TO_DATE function

The other method in MySQL to convert a string into the date format is using the str_to_date function. This function scans the input string and matches it with the date format and returns the string in the date format. If the value in the string is invalid for the format of the date then it will return a NULL value. The general syntax of this function is:

SELECT STR_TO_DATE([string],[date_format]);

Let’s say we want to convert the string ‘12,3,2021’ in the format of date, we will run the following command.

SELECT STR_TO_DATE('12,3,2021','%d,%m,%Y');

If we give it a string that contains some extra characters other than the date, it will scan the string, read the string, match it with the string, and ignore the remaining characters.

SELECT STR_TO_DATE(‘25,07,2008 hey!!!’,‘%d,%m,%Y’);

From the output, we can see that the other characters which are not included in the date. Now if we give it the invalid string, let’s say, ‘32,4,2013’ to convert in the form of date.

SELECT STR_TO_DATE('32,4,2013','%d,%m,%Y');

It gives the NULL value in the output because the 32 is the invalid date of the month.

Conclusion

Functions provide ease to execute the statement, there are default functions in MySQL which can be used for the conversion of the date in a string expression to the date format. This article will assist you in understanding the conversion of date in a string expression to date format using the built-in function, cast(). The general syntax of using the cast() function in MySQL has been discussed in the article with the help of examples.

How to use MySQL cast function to convert string to date

What is the cast string to date in MySQL

How to use the STR_TO_DATE function

Conclusion

About the author

Hammad Zahid