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: SQL Server 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