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

Mapping SQL Server Datetime Style to MySQL Format

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

SQL Server Style MySQL Format String String Example
101 US - MM/DD/YYYY '%m/%d/%Y' '11/29/2012'
121 ODBC - YYYY-MM-DD HH:MI:SS.FFF '%Y-%m-%d %T.%f' '2012-11-29 18:21:11.123'

Conversion examples:

SQL Server MySQL
CONVERT(DATETIME, '11/29/2012', 101) STR_TO_DATE('11/29/2012', '%m/%d/%Y')
CONVERT(DATETIME, '2012-11-29 18:21:11.123', 121) STR_TO_DATE('2012-11-29 18:21:11.123', '%Y-%m-%d %T.%f')

SQL Server CONVERT for SMALLDATETIME in MySQL

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

Database and SQL Migration Tools

You could leave a comment if you were logged in.