Modern databases often implement some logic through stored procedure, functions and triggers and this part is most hard for migration to another database management system.
Among all functions of SQL Server CONVERT is one of the most complicated for migration to other DBMS like PostgreSQL and MySQL. This whitepaper explores how to convert SQL function depending on number and type of its parameters.
The SQL Server function CONVERT offers the ability to convert data between diverse types and to format it based on a specified format mask. The fundamental syntax of this function is illustrated below, parameters length and style are optional.:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
Convert DATETIME to String
If first parameter of Convert SQL function is VARCHAR and expression has date, datetime, time or timestamp type, the function is expected to return text representation of the expression based on the specified style argument. This variation of function is converted into PostgreSQL TO_CHAR(expression, format) or MySQL DATE_FORMAT(expression, format) equivalents. For both destination DBMS format must be selected based on the original SQL Server style according to this table:
| SQL Server | PostgreSQL | MySQL |
| 1 | mm/dd/yy | %m/%d/%y |
| 2 | yy.mm.dd | %y.%m.%d |
| 3 | dd/mm/yy | %d/%m/%y |
| 4 | dd.mm.yy | %d.%m.%y |
| 5 | dd-mm-yy | %d-%m-%y |
| 6 | dd Mon yy | %d %b %y |
| 7 | Mon dd, yy | %b %d, %y |
| 10 | mm-dd-yy | %m-%d-%y |
| 11 | yy/mm/dd | %y/%m/%d |
| 101 | mm/dd/yyyy | %m/%d/%Y |
| 102 | yyyy.mm.dd | %Y.%m.%d |
| 103 | dd/mm/yyyy | %d/%m/%Y |
| 104 | dd.mm.yyyy | %d.%m.%Y |
| 105 | dd-mm-yyyy | %d-%m-%Y |
| 106 | dd Mon yyyy | %d %b %Y |
| 107 | Mon dd, yyyy | %b %d, %Y |
| 110 | mm-dd-yyyy | %m-%d-%Y |
| 111 | yyyy/mm/dd | %Y/%m/%d |
| 112 | yyyymmdd | %Y%m%d |
| 120 | yyyy-mm-dd hh24:mi:ss | %Y-%m-%d %T |
| 121 | yyyy-mm-dd hh24:mi:ss.ms | %Y-%m-%d%T.%f |
| 122 | yyyy-mm-ddThh24:mi:ss.ms | %Y-%m-%dT%T.%f |
Convert String to DATETIME
When the ‘data_type’ parameter is specified as DATE, DATETIME, or TIME, and the expression is of type VARCHAR, the CONVERT SQL function is expected to extract the date, datetime, or time from the provided literal expression using the designated formatting style. In PostgreSQL this function is translated into TO_DATE(expression, format) and in MySQL – into STR_TO_DATE(expression, format), with the format being chosen based on the SQL Server style, as indicated by the table above.
Convert Money to String
Whenthe ‘data_type’ parameter of CONVERT SQL functionis VARCHAR and expression has money type, it can be migrated into FORMAT in MYSQL or TO_CHAR in PostgreSQL with the following attributes depending on the original style:
| SQL Server | PostgreSQL | MySQL |
| 0 | 9999.99 | replace(format(expr,2),’,’,’’) |
| 1 | 9G999.99 | format(expr,2) |
| 2 | 9999.9999 | replace(format(expr,4),’,’,’’) |
The guide above proves that even migration of single function may require much time and efforts. It is recommended using special software to convert SQL functions from SQL Server to PostgreSQL or MySQL. One of these tools is SQL Server to PostgreSQL Code Converter developed by Intelligent Converters company.
