SQL Server CONVERT Datetime to String in MySQL

In SQL Server, you can use CONVERT function to convert a DATETIME value to a string with the specified format. In MySQL, you can use DATE_FORMAT function.

SQL Server:

  -- 3rd parameter specifies 121 style (ODBC 'YYYY-MM-DD HH:MI:SS.FFF' format with milliseconds)
  SELECT CONVERT(VARCHAR, GETDATE(), 121);
  # 2012-11-29 19:18:41.863

MySQL:

  -- Specify string format using format specifiers
  SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %T.%f');
  # 2012-11-29 19:18:41.000000

Mapping SQL Server Datetime Style to MySQL Format

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

SQL Server Style MySQL Format String Output Example
101 US - MM/DD/YYYY '%m/%d/%Y' 11/29/2012
103 British/French - DD/MM/YYYY '%d/%m/%Y' 29/11/2012
108 Time - HH:MI:SS '%T' 18:21:11
112 Date - YYYYMMDD '%Y%m%d' 2017-04-06
121 ODBC - YYYY-MM-DD HH:MI:SS.FFF '%Y-%m-%d %T.%f' 2012-11-29 18:21:11.123
20 ODBC - YYYY-MM-DD HH:MI:SS '%Y-%m-%d %T' 2012-11-29 18:21:11

Conversion examples:

SQL Server MySQL
CONVERT(VARCHAR, GETDATE(), 101) DATE_FORMAT(NOW(), '%m/%d/%Y')
CONVERT(VARCHAR, GETDATE(), 103) DATE_FORMAT(NOW(), '%d/%m/%Y')
CONVERT(VARCHAR, GETDATE(), 108) DATE_FORMAT(NOW(), '%T')
CONVERT(VARCHAR, GETDATE(), 112) DATE_FORMAT(NOW(), '%Y%m%d')
CONVERT(VARCHAR, GETDATE(), 121) DATE_FORMAT(NOW(), '%Y-%m-%d %T.%f')
CONVERT(VARCHAR, GETDATE(), 20) DATE_FORMAT(NOW(), '%Y-%m-%d %T')

Database and SQL Migration Tools

About SQLines

SQLines offers services and tools to help you migrate databases and applications. For more information, please contact us at support@sqlines.com.

You could leave a comment if you were logged in.