CAST for Datetime Function - SQL Server to MySQL Migration

In SQL Server, you can use CAST function to convert a string in various formats to a DATETIME value. In MySQL, you have to use STR_TO_DATE function and explicitly specify the format.

SQL Server:

  SELECT CAST('19-Jul-2022' AS DATETIME);
  # 2022-07-19 00:00:00.000
 
  SELECT CAST('07/19/2022' AS DATETIME);
  # 2022-07-19 00:00:00.000
 
  SELECT CAST('2022-07-19' AS DATETIME);
  # 2022-07-19 00:00:00.000

MySQL:

  SELECT CAST('19-Jul-2022' AS DATETIME(3));
  # NULL
 
  SELECT CAST('07/19/2022' AS DATETIME(3));
  # NULL
 
  SELECT CAST('2022-07-19' AS DATETIME(3));
  # 2022-07-19 00:00:00.000

You can see that SQL Server can automatically recognize many string representations for datetime values. While MySQL returns NULL (it does not fail ) in such cases, and requires the string format to be specified explicitly:

MySQL:

  SELECT STR_TO_DATE('19-Jul-2022', '%d-%b-%Y');
  # 2022-07-19
 
  SELECT STR_TO_DATE('07/19/2022', '%m/%d/%Y');
  # 2022-07-19
 
  SELECT CAST('2022-07-19' AS DATETIME(3));
  # 2022-07-19 00:00:00.000

For more information, see SQL Server to MySQL Migration