SQL Server Cast and Convert and PostgreSQL CAST and CONVERSION
Feature compatibility | AWS SCT / AWS DMS automation level | AWS SCT action code index | Key differences |
---|---|---|---|
|
|
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, butCONVERT
isn’t. -
CONVERT
accepts an optional style parameter used for formatting.
For more information, see Date and Time styles
Conversion Matrix
For a list of available conversion data types, see Implicit conversions
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)
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 |
|
|
Explicit |
|
Need to use |
|
Implicit casting |
|
Convert to a specific date format: |
|
|
For more information, see CREATE CAST