SQL Server cast and convert for T-SQL

This topic provides reference information about data type conversion and casting in Amazon Aurora PostgreSQL compared to Microsoft SQL Server. You can understand the similarities and differences between the CAST and CONVERT functions in both database systems. The topic explains how Aurora PostgreSQL supports the CAST function similarly to SQL Server, while also offering additional flexibility through custom casts and the CREATE CAST command.

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:

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.

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

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