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)