SQL Server Cast and Convert and PostgreSQL CAST and CONVERSION - SQL Server to Aurora PostgreSQL Migration Playbook
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China.

SQL Server Cast and Convert and PostgreSQL CAST and CONVERSION

Feature compatibility Amazon SCT / Amazon DMS automation level Amazon SCT action code index Key differences


                              Three star feature compatibility


                              Four star automation level

N/A

CONVERT is used only to convert between collations. CAST uses different syntax.

SQL Server Usage

The CAST and CONVERT functions are commonly used to convert one data type to another. CAST and CONVERT behave mostly the same and share the same topic in MSDN. They have the following differences:

  • CAST is part of the ANSI-SQL specification, but CONVERT isn’t.

  • CONVERT accepts an optional style parameter used for formatting.

For more information, see Date and Time styles in the SQL Server documentation.

Conversion Matrix

For a list of available conversion data types, see Implicit conversions in the SQL Server documentation.

Syntax

-- CAST Syntax:
CAST ( expression AS data_type [ ( length ) ] )

-- CONVERT Syntax:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

Examples

The following example casts a string to int and int to decimal.

SELECT CAST('23.7' AS varchar) AS int, CAST(23.7 AS int) AS decimal;

The following example converts string to int and int to decimal.

SELECT CONVERT(VARCHAR, '23.7') AS int, CONVERT(int, 23.7) AS decimal;

For these two preceding examples, the result looks as shown following.

int   decimal
23.7  23

The following example converts a date with option style input (109 - mon dd yyyy hh:mi:ss:mmmAM (or PM)).

SELECT CONVERT(nvarchar(30), GETDATE(), 109);

Jul 25 2018 5:20:10.8975085PM

For more information, see CAST and CONVERT (Transact-SQL) in the SQL Server documentation.

PostgreSQL Usage

Amazon Aurora PostgreSQL-Compatible Edition (Aurora PostgreSQL) provides the same CAST function as SQL Server for conversion between data types. It also provides a CONVERSION function, but it isn’t equivalent to SQL Server CONVERT. PostgreSQL CONVERSION is used to convert between character set encoding.

CREATE A CAST defines a new cast on how to convert between two data types.

Cast can be EXPLICITLY or IMPLICIT.

The behavior is similar to SQL Server’s casting, but in PostgreSQL, you can also create your own casts to change the default behavior. For example, checking if a string is a valid credit card number by creating the CAST with the WITHOUT FUNCTION clause.

CREATE CONVERSION is used to convert between encoding such as UTF8 and LATIN. If CONVERT is currently in use in SQL Server code, rewrite it to use CAST instead.

Note

Not all SQL Server data types are supported on Aurora PostgreSQL, besides changing the CAST or CONVERT commands, you might need to also change the source of the target data type. For more information, see Data Types.

Another way to convert between data types in PostgreSQL will be to use the :: characters. This option is useful and can make your PL/pgSQL code look cleaner and simpler, see the following examples.

Syntax

CREATE CAST (source_type AS target_type)
WITH FUNCTION function_name (argument_type [, ...]) [ AS ASSIGNMENT | AS IMPLICIT ]

CREATE CAST (source_type AS target_type)
WITHOUT FUNCTION [ AS ASSIGNMENT | AS IMPLICIT ]

CREATE CAST (source_type AS target_type)
WITH INOUT [ AS ASSIGNMENT | AS IMPLICIT ]

Examples

The following example converts a numeric value to float.

SELECT 23 + 2.0;

or

SELECT CAST ( 23 AS numeric ) + 2.0;

The following example converts a date with format input ('mon dd yyyy hh:mi:ss:mmmAM (or PM)').

SELECT TO_CHAR(NOW(),'Mon DD YYYY HH:MI:SS:MSAM');

Jul 25 2018 5:20:10.8975085PM

The following example uses the :: characters.

SELECT '2.35'::DECIMAL + 4.5 AS results;

results
6.85

Summary

Option SQL Server Aurora PostgreSQL

Explicit CAST

SELECT CAST('23.7' AS varchar) AS int

SELECT CAST('23.7' AS varchar) AS int

Explicit CONVERT

SELECT CONVERT (VARCHAR, '23.7')

Need to use CAST:

SELECT CAST('23.7' AS varchar) AS int

Implicit casting

SELECT 23 + 2.0 SELECT 23 + 2.0

Convert to a specific date format: 'mon dd yyyy hh:mi:ss:mmmAM'

SELECT CONVERT(nvarchar (30), GETDATE(), 109)

SELECT TO_CHAR(NOW(),'Mon DD YYYY HH:MI:SS:MSAM')

For more information, see CREATE CAST, Type Conversion, and CREATE CONVERSION in the PostgreSQL documentation.