SQL Server CONVERT for Datetime in MySQL

In SQL Server, you can use CONVERT function to convert a string with the specified format to a DATETIME value. In MySQL, you can use STR_TO_DATE function if you need a specific format, or CONVERT if you need the default format.

Note that the order of parameters in SQL Server and MySQL CONVERT functions is different.

SQL Server:

  -- 3rd parameter specifies 121 style (ODBC 'YYYY-MM-DD HH:MI:SS.FFF' format with milliseconds)
  SELECT CONVERT(DATETIME, '2012-11-29 18:21:11.123', 121);
  # 2012-11-29 18:21:11.123
 
  SELECT CONVERT(DATETIME, GETDATE());
  # 2017-04-07 09:55:40.550

MySQL:

  -- Specify string format using format specifiers
  SELECT STR_TO_DATE('2012-11-29 18:21:11.123', '%Y-%m-%d %T.%f');
  # 2012-11-29 18:21:11.123000
 
   SELECT CONVERT(NOW(), DATETIME);
   # 2017-04-07 09:55:40

When you convert CONVERT function to STR_TO_DATE you have to map the SQL Server style to the appropriate format string in MySQL:

Conversion examples:

In SQL Server SMALLDATETIME data type stores a datetime value with 00 seconds. You can use the expression below to keep 00 seconds after using the CONVERT function in MySQL:

SQL Server:

  -- SMALLDATETIME is always with 00 seconds
  SELECT CONVERT(SMALLDATETIME, GETDATE());
  # 2017-04-07 10:05:00

MySQL:

   SELECT CONVERT(DATE_FORMAT(NOW(), '%Y-%m-%d %H-%i-00'), DATETIME);
   # 2017-04-07 10:05:00

• SQL Server to MySQL Migration Tools and Reference