The available data types are:
Integer
LongInteger
Real
SingleReal
Currency
Date
DateTime
Time
TimeSpan
Boolean
String
Binary
All data formats except Currency (Decimal) use a binary floating-point number representation of the values. This means that some calculations which should result in an even number may be displayed as a number which needs to be rounded off, due to the nature of the base-two calculation. When more calculations are performed after one another, errors can accumulate and may become a problem.
|
Data Type |
Description |
|
Integer values are written as a sequence of digits, possibly prefixed by a + or - sign. The integer values that can be specified range from -2147483648 to 2147483647. If used where a decimal value was expected, the integer values are automatically converted to decimal values. Note: Hexadecimal values can be used in custom expressions and in calculated columns. They cannot be used when opening data. Hexadecimal-formatted values have a size limitation of 8 characters. Examples: 0 |
|
|
LongInteger can be used if the range for the standard Integer is not enough for your needs. It ranges from -9223372036854775808 to 9223372036854775807. LongInteger cannot be converted to Real without precision loss, but it can be converted to Currency without precision loss. Note: Hexadecimal values can be used in custom expressions and in calculated columns. They cannot be used when opening data. Examples: 2147483648 0x7FFFFFFFFFFFFFFF = -9223372036854775808 0x8000000000000000 = 9223372036854775807 |
|
|
Real values are written as standard floating point numbers with a period for a decimal point and no thousands separator. The real values that can be specified range from -8.98846567431157E+307 to 8.98846567431157E+307. The number of significant digits that can be shown is limited to 15, even though 16 can be used in calculations. Math operations on real values which produce results that cannot be represented by the real data type generate numeric errors. In the resulting data table, these special cases will be filtered out and replaced by null. Examples: 0.0 |
|
|
SingleReal values are written as standard floating point numbers with lower precision and range than Real. SingleReal occupies 50% less memory than Real. The SingleReal values that can be specified range from -1.7014117E+38 to 1.7014117E+38. The number of significant digits that can be shown is limited to 7, even though 8 can be used in calculations. SingleReal can be converted to Real with minor precision loss. |
|
|
Currency constants are written as integer or real constants with an 'm' suffix. The data format behind the currency type is decimal. The decimal data format uses the base 10 in its calculations, which means that the round-off errors that may occur when doing binary calculations can be avoided with this format. However, this also means that heavy calculations take a longer time. The number of significant digits that can be shown for a currency value is 28 (29 can be used in calculations). Currency values that can be specified range from -39614081257132168796771975168 to 39614081257132168796771975168. Currency columns cannot be used in data functions. |
|
|
A date and time format depending on the locale on your computer. Dates from January 1, 1583 and forward are supported. Examples: 6/12/2006 June 12 June, 2006 Note that the Date format is not directly supported by Spotfire Statistics Services. See also How to Use Data Functions. |
|
|
A date and time format depending on the locale on your computer. Dates from January 1, 1583 and forward are supported. Examples: 6/12/2006 Monday, June 12, 2006 1:05 PM 6/12/2006 10:14:35 AM |
|
|
A date and time format depending on the locale on your computer. Examples: 2006-06-12 10:14:35 10:14 10:14:35 Note that the Time format is not directly supported by Spotfire Statistics Services. See also How to Use Data Functions. |
|
|
TimeSpan is a value describing the difference between two dates. It has 5 possible fields: Days 1. Min: -10675199 2. Max: 10675199 Hours 1. Min: 0 2. Max: 23 Minutes 1. Min: 0 2. Max: 59 Seconds 1. Min: 0 2. Max: 59 Fractions (decimals of seconds) 1. Up to three decimals, i.e., the precision is 1 ms. TimeSpan values can be displayed on a compact form: [-]d.h:m:s.f ([-]days.hours:minutes:seconds.fractions) or written out with words or abbreviations for each available field. Some of the descriptive forms can be localized. Total min: -10675199.02:48:05.477 Total max: 10675199.02:48:05.477 |
|
|
True and false. Booleans are used to represent true and false values returned by comparison operators and logical functions. The display values can be localized. Examples: true |
|
|
String values are surrounded by double quotes or single quotes. Escaping is performed by entering the delimiter symbol twice in a row (i.e., '' or ""). A string value can contain any sequence of UNICODE characters. A double quote cannot be used within the string unless it is escaped. Backslash is used to escape special characters, so it too must be escaped. The basic escaping rules are that only the characters defined below can be used after a \; everything else will generate an error. Examples: "Hello world" |
|
|
May contain any type of data, encoded in binary form. Examples: Images Chemical structure information |
|
Escape sequence |
Result |
|
\uHHHH |
Any Unicode character expressed as four hexadecimal characters, 0-F. |
|
\DDD |
A character in the range 0-255 expressed as three octal digits, 0-7. |
|
\b |
\u0008: backspace (BS) |
|
\t |
\u0009: horizontal tab (HT) |
|
\n |
\u000a: linefeed (LF) |
|
\f |
\u000c: form feed (FF) |
|
\r |
\u000d: carriage return (CR) |
|
\\ |
\u005c: backslash \ |
Conversion to other data types
The data types supported in expressions are the same types as are supported in the data model. Converting a value from one data type to another is called casting.
Implicit casting to real is performed when integer columns are used in calculations and the result is a non-integer. If the result is an integer but larger than the limit for the Integer data type, it will be implicitly cast to a LongInteger. Integers can also be implicitly cast to a Currency. For example, if an Integer and a Currency column are added, then the result will be a Currency column.
You may also end up with a Currency when the result from a LongInteger exceeds the LongInteger limit. This is because a LongInteger cannot be cast to Real without the risk of losing precision. All operations using TimeSpan (except a simple TimeSpan casting) will return a DateTime. For any other conversions, you need to use the Conversion Functions to calculate new columns or use in custom expressions. Binary objects cannot be cast to any other data types.
Conversion of the data types for several columns simultaneously can be done using the Change Data Types transformation tool. Transformations can be done on existing data tables via the Add Data Tables dialog or the Replace Data Table dialog.
See also: