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 ​

Supported Conversions ​

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)