CONVERT (SQL Server Migration Function)

Cast an expression to a specified data type using a specific style.
Note: The CONVERT function is rewritten to standard SQL and is intended only for use during migration from a Microsoft SQL Server application. See the Supported Conversions table for the exact rewrites. For all purposes other than migration, Yellowbrick strongly recommends that you use the standard SQL functions directly. 
CONVERT(datatype, expr [, style])
datatype
Target data type
expr
Input expression to be converted to datatype
style
Integer constant that identifies the type of input or output. See the Style Patterns table. If style is not provided, a default style is chosen based on the datatype.

Style Patterns

Style Pattern Notes
0, 100 Mon DD YYYY HH12:MIAM
1 MM/DD/YY US
101 MM/DD/YYYY US
2 YY.MM.DD ANSI
102 YYYY.MM.DD ANSI
3 DD/MM/YY British/French
103 DD/MM/YYYY British/French
4 DD.MM.YY German
104 DD.MM.YYYY German
5 DD-MM-YY Italian
105 DD-MM-YYYY Italian
6 DD Mon YY
106 DD Mon YYYY
7 Mon DD, YY
107 Mon DD, YYYY
8, 24, 108 HH24:MI:SS
9, 109 Mon DD YYYY HH12:MI:SS:MSAM
10 MM-DD-YY USA
110 MM-DD-YYYY USA
11 YY/MM/DD Japan
111 YYYY/MM/DD Japan
12 YYMMDD ISO
112 YYYYMMDD ISO
13, 113 DD Mon YYYY HH24:MI:SS:MS Europe default + milliseconds
14, 114 HH24:MI:SS:MS
20, 120 YYYY-MM-DD HH24:MI:SS ODBC canonical
21, 25, 121 YYYY-MM-DD HH24:MI:SS.MS ODBC canonical + milliseconds, also default style in the two argument version
22 MM/DD/YY HH12:MI:SS AM US
23 YYYY-MM-DD ISO8601
126 YYYY-MM-DD"T"HH24:MI:SS.MS ISO8601
127 Unsupported ERROR: convert: ISO8601 with time zone UTC unsupported
130, 131 Unsupported ERROR: convert: Hijri calendar unsupported

Supported Conversions

From To Rewrite
unknown, char, varchar, text date to_date(expr::text, style_pattern)
unknown, char, varchar, text timestamp to_timestamp(expr::text, style_pattern)::timestamp
unknown, char, varchar, text timestamptz to_timestamp(expr::text, style_pattern)
unknown, char, varchar, text float4, float8 cast(expr as datatype)

The two argument form of CONVERT is allowed, but the three argument form will result in an error.
unknown, char, varchar, text time, interval error: convert: cannot convert from exprtype to datatype
date, timestamp, timestamptz char, varchar, text to_char(expr, style_pattern)::datatype
float4, float8 char, varchar, text cast(expr as datatype)

The two argument form of CONVERT is allowed, but the three argument form will result in an error.
time, interval char, varchar, text error: convert: cannot convert from exprtype to datatype
numeric float4, float8 error: convert: cannot convert from exprtype to datatype
Any CONVERT function that is not listed above devolves to CAST(expr as datatype).

Usage Notes

  • SQL Server date and datetime2 types correlate well with Yellowbrick date and timestamp types respectively.
  • However, SQL Server datetimeoffset type does not correlate well with Yellowbrick timestamptz type. In this case, use standard SQL functions directly rather than CONVERT.
  • CONVERT does not support arbitrary whitespace or / as a delimiter. To work around this, use REPLACE to prepare the input. For example, the inputs 2000/01/01 and 2001 - 01 - 01 would have to be prepared in the following way: 
    SELECT CONVERT(date, '2001-01-01', 120) x, CONVERT(date, REPLACE('2001/01/01', '/', '-'), 120) y, 
CONVERT(date, REPLACE('2001 - 01 - 01', ' ', ''), 120) z;

Examples

The following example converts varchar Mar 31, 22, which is in the style 7 pattern, to a character string date:
premdb=# SELECT CONVERT(date, 'Mar 31, 22', 7);
    date    
------------
 2022-03-31
(1 row)
The opposite can be done by starting with a date and extracting a varchar in a specified format:
premdb=# SELECT CONVERT(varchar(100), CAST('2001-01-01' as date), 7);
  varchar   
------------
 Jan 01, 01
(1 row)
Attempting to convert unrelated datatypes will result in an error:
premdb=# SELECT CONVERT(float8, '1s'::interval);
ERROR:  cannot cast type interval to double precision
LINE 1: SELECT CONVERT(float8, '1s'::interval);
               ^
Attempting to use a random integer as a style will result in an error in cases where style matters. If style is not needed, the random integer will be ignored. 
premdb=# SELECT CONVERT(date, '2001-01-01', 3000);
ERROR:  convert: invalid style 3000
premdb=# SELECT CONVERT(int8, 1::int2, 3000);
 int8 
------
    1
(1 row)