8. Automatic conversion between different value types
Prev | Chapter 3. The language used to write formulas | Next

## 8. Automatic conversion between different value types

The behavior of operators such as +, *, =, <>, <=, >, etc, is the one of equivalent XPath operators.

Surprising example: using = and <> to compare node-sets:

1 | two
two | 3

(A:A = B:B), (A:A = B1) and (A:A <> B2) all work and evaluate to true.

Fortunately the behavior of spreadsheet function such as sum(), rounddown(), etc, is almost identical to the behavior of similar functions found in other spreadsheet software. This behavior has no XPath equivalent.

The automatic conversion process is the one described in the XPath standard.

From\To | Number | String | Boolean | Nodeset | Date / Time / DateTime
--- | --- | --- | --- | --- | ---
Number | - | Conversion does not add superfluous zeros after the point (e.g. it generates 1, not 1.0). Scientific notation (e.g. 314E-2) is never used. | 0 and NaN are converted to FALSE. Other numbers are converted to TRUE. | ERROR | A number is taken to be the number of seconds since January 1, 1970, 00:00:00 GMT. This number is converted to the corresponding datetime.

String

Strings such as 3.14, -2 can be parsed as numbers.

Strings such as 314E-2 or 1,000,000.0 cannot be parsed as numbers (use function numbervalue() to do this).

A string which cannot be parsed as a number is converted to NaN.

-

A string is TRUE if its length is non-zero.

ERROR

Strings using the ISO 8601 format (also used by W3C XML Schema Datatypes) are successfully converted to date, time and datetime.

Examples: 1960-03-15Z (date), 13:30:00Z (time), 1960-03-16T12:30:00Z (datetime)

Boolean | TRUE is converted to 1. FALSE is converted to 0. | TRUE is converted to "true". FALSE is converted to "false". | - | ERROR | ERROR

Nodeset

A nodeset is first converted to a string and then this string is converted to a boolean.

String value of the node in the nodeset that is first in document order.

Text contained in descendant nodes of this first node is taken into account. Except that text contained in comments and processing-instructions is ignored.

Example: `<ul><li>The <b>little</a></li><li> <!--pussy-->cat </li><li>is chasing a mouse.</li></ul>` converted to a string gives "`The little cat is chasing a mouse.`".

A nodeset is TRUE if it is non empty.

-

ERROR

Date / Time / DateTime

Date, time and datetime are converted to the number of seconds since January 1, 1970, 00:00:00 GMT.

(The ``date used for a time'' is January 1, 1970 GMT.)

Date, time and datetime are represented using the ISO 8601 format (also used by W3C XML Schema Datatypes).

Examples: 1960-03-15Z (date), 13:30:00Z (time), 1960-03-16T12:30:00Z (datetime)

Date, time and datetime are always TRUE.

ERROR

-

Prev | Up | Next
7. Value types | Home | Chapter 4. Predefined functions