CONVERT Datetime to String - SQL Server to MariaDB Migration

In SQL Server you can use CONVERT function to convert a DATETIME value to a string with the specified style (string format). In MariaDB you can use the DATE_FORMAT function.

SQL Server:

  -- 3rd parameter specifies 112 style (Date 'YYYYMMDD' format)
  SELECT CONVERT(CHAR(8), GETDATE(), 112);
  # 20170406

MySQL:

  -- Specify string format using format specifiers
  SELECT DATE_FORMAT(NOW(), '%Y%m%d');
  # 20170406

Mapping SQL Server Datetime Style to MariaDB Format

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

SQL Server Style MariaDB 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
113 European - DD MON YYYY HH:MI:SS.FFF '%d %b %Y %T.%f' 15 Jul 2022 21:24:51:707
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 MariaDB
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(), 113) DATE_FORMAT (NOW(), '%d %b %Y %T.%f')
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