Home > Documentation > MS SQL to MySQL

How to translate calls of CONVERT(DATETIME) from MS SQL to MySQL

SQL Server provides CONVERT(DATETIME, string, format) function to convert strings into a DATETIME value using the specified format. MySQL, MariaDB and Percona offer STR_TO_DATE(string, format) function for the same purpose.

The table below illustrates how to replace MS SQL format specification in MySQL:

SQL Server MySQL Comments
101 %m/%d/%Y U.S. default (mm/dd/yyyy)
102 %Y.%m.%d ANSI format (yyyy.mm.dd)
103 %d/%m/%Y British/French (dd/mm/yyyy)
104 %d.%m.%Y German (dd.mm.yyyy)
105 %d-%m-%Y Italian (dd-mm-yyyy)
110 %m-%d-%Y USA format (mm-dd-yyyy)
111 %Y/%m/%d Japan format (yyyy/mm/dd)
112 %Y%m%d ISO format (yyyymmdd)
120 %Y-%m-%d %H-%i-%s ODBC (yyyy-mm-dd hh24:mi:ss)
121 %Y-%m-%d %T.%f ODBC with milliseconds

Note: MS SQL format codes 101 ... 112 have similar codes reduces by 100: 1 ... 12 that means the same output without century. For example, code 1 means mm/dd/yy. Such format codes must use the same MySQL format specification except replacing '%Y' by '%y'.

Have any questions? Contact us