Using Conversion Functions
BI Server supports functions to provide type-conversion and formatting functionality.
Using the CAST Function
Converts an expression of one data type to another. The syntax is:
cast(expression AS data_type)
For the list of data types supported by BI Server, refer to the Data Types topic. The following query casts a double value to integer.
Example:
SELECT cast(1.232 AS Int32) --returns 1
Converting strings to numeric types
When converting strings to another type, the conversion is performed using the culture from the data model’s collation. For more information about the data model’s collation, please refer to the Culture and Collation topic. Assuming that the data model’s culture is en-us, the following query converts the string 1.143 to a Double.
Example:
SELECT cast('1.143' AS Double) --returns 1.143
It is possible to override the data model’s collation used by the conversion by using the COLLATE keyword. The following query converts the string 1,143 from the it-it culture (which uses the comma as a decimal separator) to a Double.
Example:
SELECT cast('1,143' COLLATE it-it_ci AS Double) --returns 1.143
Converting strings to date/time
Conversion from string to DateTime or DateTimeOffset can accept the input in different formats. As previously explained, the culture used for the conversion is the culture of the data model’s collation, and it is possible to override the culture by using the COLLATE keyword.
The following queries convert a date-only string conforming to ISO 8601 and a date/time string using en-US culture to DateTime values.
Examples:
SELECT cast('2021-09-28' AS DateTime)
SELECT cast('09/28/2021 1:41 PM' AS DateTime)
The following query converts an ISO 8601 date/time string with offset information to DateTimeOffset.
Example:
SELECT cast('2021-09-28T17:43:12.123-05' AS DateTimeOffset)
Converting strings to TimeSpan
When converting a string to a TimeSpan, use the following format:
[-]{ d | [d.]hh:mm[:ss[.ff]] }
Elements in square brackets are optional; the vertical bar (|) represents a choice between the left or the right option. The milliseconds separator is culture-specific, and the culture used for the conversion is the culture of the data model’s collation.
The following queries show a few examples of conversion to TimeSpan.
Examples:
SELECT cast('6' AS TimeSpan) --returns 6 days
SELECT cast('6:12' AS TimeSpan) --returns 6 hours, 12 minutes
SELECT cast('6:12:14' AS TimeSpan) --returns 6 hours, 12 minutes, 14 seconds
SELECT cast('1.6:12:14' AS TimeSpan) --returns 1 day, 6 hours, 12 minutes, 14 seconds
SELECT cast('6:12:14.123' AS TimeSpan) --returns 6 hours, 12 minutes, 14 seconds, 123 milliseconds
SELECT cast('6:12:14,123' COLLATE it-it_ci AS TimeSpan) --returns 6 hours, 12 minutes, 14 seconds, 123 milliseconds
Converting strings to Boolean
Input strings supported for conversion to Boolean are either “true” or “false” (case-insensitive). The following queries show conversion from string to Boolean.
Examples:
SELECT cast('true' AS Boolean) --returns true
SELECT cast('false' AS Boolean) --returns false
Converting from other types to String
When converting other types to String the data model’s collation is used to format the output string. The following query converts the double 1.143 to String when the data model’s culture is en-US.
Example:
SELECT cast(1.143 AS String) --returns "1.143"
It is not possible to specify a custom culture for the conversion, however, the format of the converted string can be customized using the CONVERT function.
Converting numeric types to DateTime or DateTimeOffset
When converting a number to DateTime, the value will be interpreted as a number of days elapsed since January 1, 1900 at 12:00 AM. If the value has a fractional part, it will be interpreted as the fractional part of a day. For example, 4.5 is equivalent to 4 days, 12 hours, 0 minutes, 0 seconds, and 0 milliseconds.
The following queries show a few examples of conversion to DateTime.
Examples:
SELECT cast(0 AS DateTime) --returns January 1, 1900, 12:00 AM
SELECT cast(1 AS DateTime) --returns January 2, 1900, 12:00 AM
SELECT cast(2.25 AS DateTime) --returns January 3, 1900, 6:00 AM
The conversion takes into account leap years and the number of days in a month when performing the date arithmetic. Converting DateTime values to numbers assumes that January 1, 1900 at 12:00 AM is the zero value, as shown in the following examples.
Examples:
SELECT cast(cast('1900-01-01' AS DateTime) AS Int32) --returns 0
SELECT cast(cast('1899-12-31' AS DateTime) AS Int32) --returns -1
SELECT cast(cast('1900-01-02' AS DateTime) AS Int32) --returns 1
Converting numeric types to TimeSpan
When converting a number to TimeSpan, the value will be interpreted as the number of milliseconds. If the value has a fractional part, it will be interpreted as a fraction of a millisecond. For example, 1.5 is equivalent to 1 millisecond and 5 thousandths of a second. The following queries show a few examples of conversion to TimeSpan.
Examples:
SELECT cast(100 AS TimeSpan) --returns 00:00:00.1000
SELECT cast(100.1 AS TimeSpan) --returns 00:00:00.1001
SELECT cast(1000 AS TimeSpan) --returns 00:00:01.0000
CHANGETYPE, TOTYPE
Converts an expression of one data type to another. CHANGETYPE and TOTYPE can be used interchangeably. The syntax is:
totype(expression, 'data_type_as_string')
The string representing the data type must be a constant (no column references or expressions allowed) from one of the following case-insensitive values:
- string
- bool or boolean
- byte
- sbyte
- int16 or short
- uint16 or ushort
- int32 or int
- uint32 or uint
- int64 or long
- uint64 or ulong
- single or float
- double
- decimal
- timespan
- datetime
The following query casts a double value to an integer.
Example:
SELECT totype(1.234, 'int') --returns 1
Using the CONVERT Function
The CONVERT function converts an expression of one data type to another. The syntax is:
convert(expression AS data_type, 'format_string')
The CONVERT function provides the exact same functionality as the CAST function, with the difference that when converting to String, the format_string parameter can be used to customize the string output. Passing an empty format string makes CONVERT behave as CAST when converting to String. For conversion to all other data types, the format_string parameter is ignored. All conversions are performed using the culture of the data model’s collation, and it is not possible to specify a custom culture for the conversion.
The following query formats the UnitPrice of products with the currency format. The currency symbol used depends on the data model’s collation.
Example:
SELECT convert(UnitPrice AS String, 'c') FROM Products
The following query formats the ProductID of products by adding leading zeros so that the result always has 4 digits.
Example:
SELECT convert(ProductID AS String, 'D4') FROM Products
For more information on format strings, please refer to the .NET documentation:
- Standard numeric format strings
- Custom numeric format strings
- Standard date and time format strings
- Custom date and time format strings
- Standard TimeSpan format strings
- Custom TimeSpan format strings
Miscellaneous Conversion Functions
BI Server supports additional conversion functions, such as:
TOBOOLEAN
Converts an expression to Boolean. If the source expression is a string, it is always interpreted using the invariant culture, even if the COLLATE keyword is used. The following queries show a few examples of the TOBOOLEAN function.
Examples:
SELECT toboolean(0) --returns false
SELECT toboolean('true') --returns true
SELECT toboolean('1.1') --returns true in any culture
SELECT toboolean('1,1') --fails because the conversion uses the invariant culture
SELECT toboolean('1,1' COLLATE it-it_ci) --also fails because the conversion always uses the invariant culture, and COLLATE does not override it
TOBOOLEANCULTURE
Converts an expression to Boolean. If the source expression is a string, it is interpreted using the culture of the data model’s collation. The culture used for the conversion can be overridden by applying the COLLATE keyword. The following queries show a few examples of the TOBOOLEANCULTURE function.
Examples:
SELECT tobooleanculture(0) --returns false
SELECT tobooleanculture('true') --returns true
SELECT tobooleanculture('1.1') --returns true in the en-us culture
SELECT tobooleanculture('1,1') --fails in the en-us culture
SELECT tobooleanculture('1,1' COLLATE it-it_ci) --returns true
TODATETIME
Converts an expression to DateTime. If the source expression is a string, it is always interpreted using the invariant culture, even if the COLLATE keyword is used. The following queries show a few examples of the TODATETIME function.
Example:
SELECT todatetime(0) --returns January 1, 1900 12:00 AM
Examples:
SELECT todatetime('2021-09-29') --returns September 29, 2021 12:00 AM
SELECT todatetime('09/29/2021') --returns September 29, 2021 12:00 AM
SELECT todatetime('29/09/2021') --fails because the conversion uses the invariant culture
SELECT todatetime('29/09/2021' COLLATE it-it_ci) --also fails because the conversion always uses the invariant culture, and COLLATE does not override it
TODATETIMECULTURE
Converts an expression to DateTime. If the source expression is a string, it is interpreted using the culture of the data model’s collation. The culture used for the conversion can be overridden by applying the COLLATE keyword. The following queries show a few examples of the TODATETIMECULTURE function.
Example:
SELECT todatetimeculture(0) --returns January 1, 1900 12:00 AM
Examples:
SELECT todatetimeculture('2021-09-29') --returns September 29, 2021 12:00 AM
SELECT todatetimeculture('09/29/2021') --returns September 29, 2021 12:00 AM in the en-us culture
SELECT todatetimeculture('29/09/2021') --fails in the en-us culture
SELECT todatetimeculture('29/09/2021' COLLATE it-it_ci) --returns September 29, 2021 12:00 AM
TOFORMAT
Converts an expression to a String using the provided format string. The culture used for the conversion is always the invariant culture, and it is not possible to specify a custom collation for the conversion. The following queries show a few examples of the TOFORMAT function.
Examples:
SELECT toformat(UnitPrice, 'c') FROM Products --returns prices with the invariant culture currency symbol ¤
SELECT toformat(ProductID, 'D4') FROM Products --returns product IDs as 4 digits with leading zeroes
TOFORMATCULTURE
Converts an expression to a String using the provided format string. The culture used for the conversion is the culture of the data model’s collation, and it is not possible to specify a custom collation for the conversion. The following queries show a few examples of the TOFORMATCULTURE function.
Examples:
SELECT toformatculture(UnitPrice, 'c') FROM Products --returns prices with the currency symbol $ in en-us culture
SELECT toformatculture(ProductID, 'D4') FROM Products --returns product IDs as 4 digits with leading zeroes
TONUMBER
This function converts an expression to a Double. If the source expression is a string, it is always interpreted using the invariant culture, even if the COLLATE keyword is used. The following queries show a few examples of the TONUMBER function.
Example:
SELECT tonumber('1') --returns 1 (double)
Note: The Expression Engine returns an Int64 for strings that represent integers, whereas BI Server always returns Double.
Examples:
SELECT tonumber('1.1') --returns 1.1 in any culture
SELECT tonumber('1,1') --fails because the conversion uses the invariant culture
SELECT tonumber('1,1' COLLATE it-it_ci) --also fails because the conversion always uses the invariant culture, and COLLATE does not override it
TONUMBERBASE
Converts a string, which represents an integral number in the specified base, to Int64. The conversion does not use culture information for the conversion. The following queries show a few examples of the TONUMBERBASE function.
Examples:
SELECT tonumberbase('11001', 2) --returns 25 (from binary 11001)
SELECT tonumberbase('10', 8) --returns 8 (from octal 10)
SELECT tonumberbase('18', 10) --returns 18 (from decimal 18)
SELECT tonumberbase('18.1', 10) --fails
Example:
SELECT tonumberbase('1a', 16) --returns 26 (from hexadecimal 1a)
TONUMBERCULTURE
Converts an expression to Double. If the source expression is a string, it is interpreted using the culture of the data model’s collation. The culture used for the conversion can be overridden by applying the COLLATE keyword. The following queries show a few examples of the TONUMBERCULTURE function.
Example:
SELECT tonumberculture('1') --returns 1 (double)
Note: The Expression Engine returns an Int64 for strings that represent integers, whereas BI Server always returns Double.
Examples:
SELECT tonumberculture('1.1') --returns 1.1 in the en-us culture
SELECT tonumberculture('1,1') --fails in the en-us culture
SELECT tonumberculture('1,1' COLLATE it-it_ci) --returns 1.1
TOSTRING
Converts an expression to a String. The culture used for the conversion is always the invariant culture, and it is not possible to specify a custom collation for the conversion. The following queries show a few examples of the TOSTRING function.
Examples:
SELECT tostring(false) --returns 'False'
SELECT tostring(1.234) --returns '1.234'
SELECT tostring(now()) --returns the current date/time formatted with the invariant culture
TOSTRINGCULTURE
Converts an expression to a String. The culture used for the conversion is the culture of the data model’s collation, and it is not possible to specify a custom collation for the conversion. The following queries show a few examples of the TOSTRINGCULTURE function.
Examples:
SELECT tostring(false) --returns 'False'
SELECT tostring(1.234) --returns '1.234' in a en-us culture
SELECT tostring(now()) --returns the current date/time formatted with the data model’s culture
TOTIMESPAN
Converts an expression to TimeSpan. If the source expression is a string, it is always interpreted using the invariant culture, even if the COLLATE keyword is used. The following queries show a few examples of the TOTIMESPAN function.
Examples:
SELECT totimespan(100) --returns 00:00:00.1000
SELECT totimespan(100.1) --returns 00:00:00.1001
SELECT totimespan(1000) --returns 00:00:01.0000
SELECT totimespan('6:12:14.123') --returns 6 hours, 12 minutes, 14 seconds, 123 milliseconds
SELECT totimespan('6:12:14,123') --fails because the conversion uses the invariant culture
TOTIMESPANCULTURE
Converts an expression to TimeSpan. If the source expression is a string, it is interpreted using the culture of the data model’s collation. The culture used for the conversion can be overridden by applying the COLLATE keyword. The following queries show a few examples of the TOTIMESPANCULTURE function.
Examples:
SELECT totimespanculture(100) --returns 00:00:00.1000
SELECT totimespanculture(100.1) --returns 00:00:00.1001
SELECT totimespanculture(1000) --returns 00:00:01.0000
SELECT totimespanculture('6:12:14.123') --returns 6 hours, 12 minutes, 14 seconds, 123 milliseconds
SELECT totimespanculture('6:12:14,123' COLLATE it-it_ci) --returns 6 hours, 12 minutes, 14 seconds, 123 milliseconds