# Type compatibility and conversion
<a name="s_Type_conversion"></a>

The following topics describe how type conversion rules and data type compatibility work in AWS Clean Rooms Spark SQL.

**Topics**
+ [Compatibility](#s_Type_conversion-compatibility)
+ [General compatibility and conversion rules](#Type_conversion-general-compatibility-and-conversion-rules)
+ [Implicit conversion types](#implicit-conversion-types-spark)

## Compatibility
<a name="s_Type_conversion-compatibility"></a>

 Data type matching and matching of literal values and constants to data types occurs during various database operations, including the following: 
+ Data manipulation language (DML) operations on tables 
+ UNION, INTERSECT, and EXCEPT queries 
+ CASE expressions 
+ Evaluation of predicates, such as LIKE and IN 
+ Evaluation of SQL functions that do comparisons or extractions of data 
+ Comparisons with mathematical operators 

The results of these operations depend on type conversion rules and data type compatibility. *Compatibility* implies that a one-to-one matching of a certain value and a certain data type is not always required. Because some data types are *compatible*, an implicit conversion, or *coercion*, is possible. For more information, see [Implicit conversion types](#implicit-conversion-types-spark). When data types are incompatible, you can sometimes convert a value from one data type to another by using an explicit conversion function. 

## General compatibility and conversion rules
<a name="Type_conversion-general-compatibility-and-conversion-rules"></a>

Note the following compatibility and conversion rules: 
+ In general, data types that fall into the same type category (such as different numeric data types) are compatible and can be implicitly converted. 

  For example, with implicit conversion you can insert a decimal value into an integer column. The decimal is rounded to produce a whole number. Or you can extract a numeric value, such as `2008`, from a date and insert that value into an integer column. 
+ Numeric data types enforce overflow conditions that occur when you attempt to insert out-of-range values. For example, a decimal value with a precision of 5 does not fit into a decimal column that was defined with a precision of 4. An integer or the whole part of a decimal is never truncated. However, the fractional part of a decimal can be rounded up or down, as appropriate. However, results of explicit casts of values selected from tables are not rounded.
+ Different types of character strings are compatible. VARCHAR column strings containing single-byte data and CHAR column strings are comparable and implicitly convertible. VARCHAR strings that contain multibyte data are not comparable. Also, you can convert a character string to a date, time, timestamp, or numeric value if the string is an appropriate literal value. Any leading or trailing spaces are ignored. Conversely, you can convert a date, time, timestamp, or numeric value to a fixed-length or variable-length character string.
**Note**  
A character string that you want to cast to a numeric type must contain a character representation of a number. For example, you can cast the strings `'1.0'` or `'5.9'` to decimal values, but you can't cast the string `'ABC'` to any numeric type.
+ If you compare DECIMAL values with character strings, AWS Clean Rooms attempts to convert the character string to a DECIMAL value. When comparing all other numeric values with character strings, the numeric values are converted to character strings. To enforce the opposite conversion (for example, converting character strings to integers, or converting DECIMAL values to character strings), use an explicit function, such as [CAST function](CAST_function.md). 
+ To convert 64-bit DECIMAL or NUMERIC values to a higher precision, you must use an explicit conversion function such as the CAST or CONVERT functions. 

## Implicit conversion types
<a name="implicit-conversion-types-spark"></a>

There are two types of implicit conversions: 
+ Implicit conversions in assignments, such as setting values in INSERT or UPDATE commands
+ Implicit conversions in expressions, such as performing comparisons in the WHERE clause

The following table lists the data types that can be converted implicitly in assignments or expressions. You can also use an explicit conversion function to perform these conversions. 

[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/clean-rooms/latest/sql-reference/s_Type_conversion.html)

**Note**  
Implicit conversions between DATE, TIME, TIMESTAMP\$1LTZ, TIMESTAMP\$1NTZ, or character strings use the current session time zone.   
The VARBYTE data type can't be implicitly converted to any other data type. For more information, see [CAST function](CAST_function.md).