Introduction:

In this blog, we will explore multiple ways to convert string to datetime in MySQL. providing step-by-step explanations and code examples for each method.

Method 1: Using the STR_TO_DATE() Function

The STR_TO_DATE() function in MySQL is a powerful tool for converting strings to DateTime. It allows us to specify the format of the input string and the desired DateTime format. Here's an example:

SELECT STR_TO_DATE('2023-08-14', '%Y-%m-%d') AS converted_date;

Output:

+---------------------+
| converted_date      |
+---------------------+
| 2023-08-14 00:00:00 |
+---------------------+

In the above query, the %Y-%m-%d format specifier indicates that the input string follows the year-month-day pattern. The STR_TO_DATE() function parses the input string accordingly and returns a DateTime value.

Method 2: Using DATE_FORMAT() with STR_TO_DATE()

This method involves using both the STR_TO_DATE() and DATE_FORMAT() functions. STR_TO_DATE() converts the string to a DateTime, and DATE_FORMAT() is then used to format the DateTime value as needed.

SELECT DATE_FORMAT(STR_TO_DATE('08/14/2023', '%m/%d/%Y'), '%Y-%m-%d') AS converted_date;

Output:

+---------------------+
| converted_date      |
+---------------------+
| 2023-08-14          |
+---------------------+

Here, we first convert the input string '08/14/2023' to a DateTime using the specified format %m/%d/%Y. Then, we format the DateTime value using the %Y-%m-%d pattern.

Method 3: Using CAST() or CONVERT()

Another way to convert strings to DateTime is by utilizing the CAST() or CONVERT() functions.

SELECT CAST('2023-08-14' AS DATETIME) AS converted_date;

SELECT CONVERT('2023-08-14', DATETIME) AS converted_date;

Output:

+---------------------+
| converted_date      |
+---------------------+
| 2023-08-14 00:00:00 |
+---------------------+

In the above queries, the CAST() and CONVERT() functions directly convert the input string '2023-08-14' to a DateTime format.

Method 4: Using UNIX_TIMESTAMP()

UNIX_TIMESTAMP() can be employed to convert Unix timestamps to DateTime.

SELECT FROM_UNIXTIME(UNIX_TIMESTAMP('2023-08-14 12:30:45')) AS converted_date;

Output:

+---------------------+
| converted_date      |
+---------------------+
| 2023-08-14 12:30:45 |
+---------------------+

In this query, the UNIX_TIMESTAMP() function converts the input string '2023-08-14 12:30:45' to a Unix timestamp, which is then converted to a DateTime using the FROM_UNIXTIME() function.

Method 5: Using Regular Expressions and Substring

If you have a custom date format, you can extract substrings and rearrange them to match the DateTime format.

SELECT CONCAT(SUBSTRING(input_date, 7, 4), '-', SUBSTRING(input_date, 1, 2), '-', SUBSTRING(input_date, 4, 2)) AS converted_date
FROM table_name;

Output:

+---------------------+
| converted_date      |
+---------------------+
| 2023-08-14          |
+---------------------+

In this query, we assume that input_date is the column containing the custom date format. We use the SUBSTRING() function to extract substrings from input_date and then concatenate them in the desired DateTime format.

Conclusion:

In this blog, we have explored several methods to convert strings to DateTime in MySQL including using functions like STR_TO_DATE(), DATE_FORMAT(), CAST(), CONVERT(), UNIX_TIMESTAMP(), and even regular expressions with substring extraction.