MetaMatrix Explained

Data Type Conversions

Overview

Data types are used in SQL commands and in scalar functions. Often, data are joined between columns of different types, or data is desired in a different type than it is represented in the source. In these cases, the types may be converted from one form to another, either explicitly or implicitly.

Some datatype conversions require explicit use of the CONVERT or CAST keyword. Others will be converted implicitly by MetaMatrix in cases where a loss of information will not occur.

The following table describes the way you can convert datatypes within MetaMatrix.

Implicit Conversion

MetaMatrix automatically adds casting logic for implicit conversions. MetaMatrix allows implicit conversion when no precision is lost, but requires explicit casting when precision may be lost. You can, however, use the CAST or CONVERT functions at your discretion in implicit conversions to explicitly use the function you want.

Note that some implicit conversions, such as from a string to a date, require a specific string literal to work. If the string differs from the required literal format, you can often find a function to perform the conversion, such as parseDate.

Special Conversion Cases

Conversion of String Literals

MetaMatrix automatically converts string literals within a SQL statement to their implied types. This typically occurs in a criteria comparison where an element with a different datatype is compared to a literal string:

SELECT * FROM my.group WHERE created_by = ‘2003-01-02’

For example, if the created_by element has the datatype of date, MetaMatrix automatically converts the string literal to a date datatype as well. Note that some literal conversions require a specific string literal to work.

Converting Literal Strings to Boolean

MetaMatrix can also automatically convert literal strings to Boolean values as follows:

Converting Floats and Integers to Boolean

You can cast floating-point numeric datatypes (float) and integers to and from Boolean datatypes. When you do so, MetaMatrix converts these datatypes as follows:

Date/Time/Timestamp Type Conversions

MetaMatrix only implicitly converts literal strings formatted as follows to the associated date-related datatypes:

Your query can also explicitly declare a datatype for a string: