Training
Module
Convert Data Types Using Casting and Conversion Techniques in C# - Training
Explore using C# techniques for casts and conversions.
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Applies to: Databricks SQL
Databricks Runtime
Azure Databricks uses several rules to resolve conflicts among data types:
You can also explicitly cast between many types:
Type promotion is the process of casting a type into another type of the same type family which contains all possible values of the original type.
Therefore type promotion is a safe operation. For example TINYINT
has a range from -128
to 127
. All its possible values can be safely promoted to INTEGER
.
The type precedence list defines whether values of a given data type can be implicitly promoted to another data type.
Data type | Precedence list (from narrowest to widest) |
---|---|
TINYINT | TINYINT -> SMALLINT -> INT -> BIGINT -> DECIMAL -> FLOAT (1) -> DOUBLE |
SMALLINT | SMALLINT -> INT -> BIGINT -> DECIMAL -> FLOAT (1) -> DOUBLE |
INT | INT -> BIGINT -> DECIMAL -> FLOAT (1) -> DOUBLE |
BIGINT | BIGINT -> DECIMAL -> FLOAT (1) -> DOUBLE |
DECIMAL | DECIMAL -> FLOAT (1) -> DOUBLE |
FLOAT | FLOAT (1) -> DOUBLE |
DOUBLE | DOUBLE |
DATE | DATE -> TIMESTAMP |
TIMESTAMP | TIMESTAMP |
ARRAY | ARRAY (2) |
BINARY | BINARY |
BOOLEAN | BOOLEAN |
INTERVAL | INTERVAL |
GEOGRAPHY | GEOGRAPHY(ANY) |
GEOMETRY | GEOMETRY(ANY) |
MAP | MAP (2) |
STRING | STRING |
STRUCT | STRUCT (2) |
VARIANT | VARIANT |
OBJECT | OBJECT (3) |
(1) For least common type resolution FLOAT
is skipped to avoid loss of precision.
(2) For a complex type the precedence rule applies recursively to its component elements.
(3) OBJECT
exists only within a VARIANT
.
Special rules apply for STRING
and untyped NULL
:
NULL
can be promoted to any other type.STRING
can be promoted to BIGINT
, BINARY
, BOOLEAN
, DATE
, DOUBLE
, INTERVAL
, and TIMESTAMP
. If the actual string value cannot be cast to least common type Azure Databricks raises a runtime error.
When promoting to INTERVAL
the string value must match the intervals units.This is a graphical depiction of the precedence hierarchy, combining the type precedence list and strings and NULLs rules.
The least common type from a set of types is the narrowest type reachable from the type precedence graph by all elements of the set of types.
The least common type resolution is used to:
Special rules are applied if the least common type resolves to FLOAT
. If any of the contributing types is an exact numeric type (TINYINT
, SMALLINT
, INTEGER
, BIGINT
, or DECIMAL
) the least common type is pushed to DOUBLE
to avoid potential loss of digits.
When the least common type is a STRING
the collation is computed following the collation precedence rules.
Azure Databricks employs these forms of implicit casting only on function and operator invocation, and only where it can unambiguously determine the intent.
Implicit downcasting
Implicit downcasting automatically casts a wider type to a narrower type without requiring you to specify the cast explicitly. Downcasting is convenient, but it carries the risk of unexpected runtime errors if the actual value fails to be representable in the narrow type.
Downcasting applies the type precedence list in reverse order. The GEOGRAPHY
and GEOMETRY
data types are never downcast.
Implicit crosscasting
Implicit crosscasting casts a value from one type family to another without requiring you to specify the cast explicitly.
Azure Databricks supports implicit crosscasting from:
BINARY
, GEOGRAPHY
, and GEOMETRY
, to STRING
.STRING
to any simple type, except GEOGRAPHY
and GEOMETRY
.Given a resolved function or operator, the following rules apply, in the order they are listed, for each parameter and argument pair:
If a supported parameter type is part of the argument's type precedence graph, Azure Databricks promotes the argument to that parameter type.
In most cases the function description explicitly states the supported types or chain, such as “any numeric type”.
For example, sin(expr) operates on DOUBLE
but will accept any numeric.
If the expected parameter type is a STRING
and the argument is a simple type Azure Databricks crosscasts the argument to the string parameter type.
For example, substr(str, start, len) expects str
to be a STRING
. Instead, you can pass a numeric or datetime type.
If the argument type is a STRING
and the expected parameter type is a simple type, Azure Databricks crosscasts the string argument to the widest supported parameter type.
For example, date_add(date, days) expects a DATE
and an INTEGER
.
If you invoke date_add()
with two STRING
s, Azure Databricks crosscasts the first STRING
to DATE
and the second STRING
to an INTEGER
.
If the function expects a numeric type, such as an INTEGER
, or a DATE
type, but the argument is a more general type, such as a DOUBLE
or TIMESTAMP
, Azure Databricks implicitly downcasts the argument to that parameter type.
For example, a date_add(date, days) expects a DATE
and an INTEGER
.
If you invoke date_add()
with a TIMESTAMP
and a BIGINT
, Azure Databricks downcasts the TIMESTAMP
to DATE
by removing the time component and the BIGINT
to an INTEGER
.
Otherwise, Azure Databricks raises an error.
The coalesce
function accepts any set of argument types as long as they share a least common type.
The result type is the least common type of the arguments.
-- The least common type of TINYINT and BIGINT is BIGINT
> SELECT typeof(coalesce(1Y, 1L, NULL));
BIGINT
-- INTEGER and DATE do not share a precedence chain or support crosscasting in either direction.
> SELECT typeof(coalesce(1, DATE'2020-01-01'));
Error: DATATYPE_MISMATCH.DATA_DIFF_TYPES
-- Both are ARRAYs and the elements have a least common type
> SELECT typeof(coalesce(ARRAY(1Y), ARRAY(1L)))
ARRAY<BIGINT>
-- The least common type of INT and FLOAT is DOUBLE
> SELECT typeof(coalesce(1, 1F))
DOUBLE
> SELECT typeof(coalesce(1L, 1F))
DOUBLE
> SELECT typeof(coalesce(1BD, 1F))
DOUBLE
-- The least common type between an INT and STRING is BIGINT
> SELECT typeof(coalesce(5, '6'));
BIGINT
-- The least common type is a BIGINT, but the value is not BIGINT.
> SELECT coalesce('6.1', 5);
Error: CAST_INVALID_INPUT
-- The least common type between a DECIMAL and a STRING is a DOUBLE
> SELECT typeof(coalesce(1BD, '6'));
DOUBLE
-- Two distinct explicit collations result in an error
> SELECT collation(coalesce('hello' COLLATE UTF8_BINARY,
'world' COLLATE UNICODE));
Error: COLLATION_MISMATCH.EXPLICIT
-- The resulting collation between two distinct implicit collations is indeterminate
> SELECT collation(coalesce(c1, c2))
FROM VALUES('hello' COLLATE UTF8_BINARY,
'world' COLLATE UNICODE) AS T(c1, c2);
NULL
-- The resulting collation between a explicit and an implicit collations is the explicit collation.
> SELECT collation(coalesce(c1 COLLATE UTF8_BINARY, c2))
FROM VALUES('hello',
'world' COLLATE UNICODE) AS T(c1, c2);
UTF8_BINARY
-- The resulting collation between an implicit and the default collation is the implicit collation.
> SELECT collation(coalesce(c1, 'world'))
FROM VALUES('hello' COLLATE UNICODE) AS T(c1, c2);
UNICODE
-- The resulting collation between the default collation and the indeterminate collation is the default collation.
> SELECT collation(coalesce(coalesce('hello' COLLATE UTF8_BINARY, 'world' COLLATE UNICODE), 'world'));
UTF8_BINARY
-- Least common type between GEOGRAPHY(srid) and GEOGRAPHY(ANY)
> SELECT typeof(coalesce(st_geogfromtext('POINT(1 2)'), to_geography('POINT(3 4)'), NULL));
geography(any)
-- Least common type between GEOMETRY(srid1) and GEOMETRY(srid2)
> SELECT typeof(coalesce(st_geomfromtext('POINT(1 2)', 4326), st_geomfromtext('POINT(3 4)', 3857), NULL));
geometry(any)
-- Least common type between GEOMETRY(srid1) and GEOMETRY(ANY)
> SELECT typeof(coalesce(st_geomfromtext('POINT(1 2)', 4326), to_geometry('POINT(3 4)'), NULL));
geometry(any)
The substring
function expects arguments of type STRING
for the string and INTEGER
for the start and length parameters.
-- Promotion of TINYINT to INTEGER
> SELECT substring('hello', 1Y, 2);
he
-- No casting
> SELECT substring('hello', 1, 2);
he
-- Casting of a literal string
> SELECT substring('hello', '1', 2);
he
-- Downcasting of a BIGINT to an INT
> SELECT substring('hello', 1L, 2);
he
-- Crosscasting from STRING to INTEGER
> SELECT substring('hello', str, 2)
FROM VALUES(CAST('1' AS STRING)) AS T(str);
he
-- Crosscasting from INTEGER to STRING
> SELECT substring(12345, 2, 2);
23
|| (CONCAT) allows implicit crosscasting to string.
-- A numeric is cast to STRING
> SELECT 'This is a numeric: ' || 5.4E10;
This is a numeric: 5.4E10
-- A date is cast to STRING
> SELECT 'This is a date: ' || DATE'2021-11-30';
This is a date: 2021-11-30
date_add can be invoked with a TIMESTAMP
or BIGINT
due to implicit downcasting.
> SELECT date_add(TIMESTAMP'2011-11-30 08:30:00', 5L);
2011-12-05
date_add can be invoked with STRING
s due to implicit crosscasting.
> SELECT date_add('2011-11-30 08:30:00', '5');
2011-12-05
Training
Module
Convert Data Types Using Casting and Conversion Techniques in C# - Training
Explore using C# techniques for casts and conversions.
Events
Ask Learn is an AI assistant that can answer questions, clarify concepts, and define terms using trusted Microsoft documentation.
Please sign in to use Ask Learn.
Sign in