How to migrate function CONVERT from SQL Server to PostgreSQL |
|
| [SQL Server to PostgreSQL Converter] [About Migration] [Types Mapping] | |
Function CONVERT in SQL Server provides capabilities of converting
data between different types as well as formatting according to the specified
format mask. Basic syntax of the function is:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
where length and style are optional. Below we consider different options of
CONVERT along with conversion into PostgreSQL.
If 'data_type' parameter is set to VARCHAR and expression has type DATE, DATETIME
or TIME, the function CONVERT generates string/text representation of the given
expression according to the specified formatting style. It is migrated into
PostgreSQL TO_CHAR(expression, format), where format is selected
based on SQL Server style as follows:
| SQL Server Style | PostgreSQL Format | |
| 1 | mm/dd/yy | |
| 2 | yy.mm.dd | |
| 3 | dd/mm/yy | |
| 4 | dd.mm.yy | |
| 5 | dd-mm-yy | |
| 6 | dd Mon yy | |
| 7 | Mon dd, yy | |
| 10 | mm-dd-yy | |
| 11 | yy/mm/dd | |
| 101 | mm/dd/yyyy | |
| 102 | yyyy.mm.dd | |
| 103 | dd/mm/yyyy | |
| 104 | dd.mm.yyyy | |
| 105 | dd-mm-yyyy | |
| 106 | dd Mon yyyy | |
| 107 | Mon dd, yyyy | |
| 110 | mm-dd-yyyy | |
| 111 | yyyy/mm/dd | |
| 112 | yyyymmdd | |
| 120 | yyyy-mm-dd hh24:mi:ss | |
| 121 | yyyy-mm-dd hh24:mi:ss.ms | |
| 122 | yyyy-mm-ddThh24:mi:ss.ms | |
If 'data_type' parameter is set to DATE, DATETIME or TIME and expression
has type VARCHAR, the function CONVERT attempts to extract date, datetime
or time from the given literal expression according to the specified
formatting style. It is migrated into PostgreSQL
TO_DATE(expression, format), where format is selected
based on SQL Server style according to the table above.
Have questions? Contact us