Scalar Functions

AnalytiX-BI supports a large number of built-in functions for arithmetic, string manipulation, date/time and more. Most of the functions supported by the ICONICS Expression Editor are also supported in AnalytiX-BI with the same name. All function names are case-insensitive.

For most functions, when a parameter is NULL, then the result of the function will also be NULL:

SELECT1 +NULL--returns NULL

SELECTsin(NULL)--returns NULL

COALESCE

Evaluates the arguments in order and returns the current value of the first expression that initially doesn’t evaluate to NULL. The resulting data type of the COALESCE function is the highest data type of its arguments.

The following query returns a comma-separated country and region for each customer, or just the country when the region is NULL.

SELECTCOALESCE(Country +', '+ Region,Country) FROMCustomers

ISNULL

Evaluates the first argument and, if it evaluates to NULL, returns the value of the second argument; otherwise returns the first argument.

The following query returns the region for each customer, and the string “no region” when the region is NULL.

SELECT ISNULL(Region,'No Region') FROMCustomers

Arithmetic Functions

Basic arithmetic operators, rounding functions, trigonometric functions, etc.

ABS

Calculates the absolute value of the input expression.

SELECTabs(ProductID - 35) FROMProducts

ACOS

Calculates the arccosine of the input expression.

SELECTacos(-1)--returnsπ

ASIN

Calculates the arcsine of the input expression.

SELECTasin(1)--returns π/2

ATAN

Calculates the arctangent of the input expression.

SELECTatan(1)--returns π/4

CEIL, CEILING

Returns the smallest integral value that is greater than or equal to the specified input expression. The result type is either Double or Decimal, depending on the input.

SELECTceil(0.1)--returns 1

SELECTceiling(0.9)--returns 1

COS

Calculates the cosine of the input expression.

SELECTcos(2 * pi())--returns 1

COT

Calculates the cotangent of the input expression.

SELECTcot(pi()/4)--returns 1

E

Returns the natural logarithmic base, specified by the constant e.

SELECTe()--returns 2.718…

EXP

Returns e raised to the specified power.

SELECTexp(2)--returns e²

FLOOR

Returns the largest integral value that is less than or equal to the specified input expression. The result type is either Double or Decimal, depending on the input.

SELECTfloor(1.1)--returns 1

SELECTfloor(1.9)--returns 1

GREATEST

Returns the maximum from the specified list of input expressions. This function supports an unlimited number of parameters. The following query retrieves, for every row, the maximum value among all columns listed and the constant 30.

SELECTgreatest(UnitsInStock,UnitsOnOrder,ReorderLevel,30) FROMProducts

ISINFINITY

Returns a Boolean indicating whether the specified expression evaluates to negative or positive infinity.

SELECTisinfinity(cot(0))--returns true

SELECTisinfinity(1)--returns false

ISNAN

Returns a Boolean indicating whether the specified value is not a number. Floating-point operations return NaN to signal that the result of the operation is not defined.

SELECTisnan(0 / 0.0)--returns true

LEAST

Returns the minimum from the specified list of input expressions. This function supports an unlimited number of parameters. The following query retrieves, for every row, the minimum value among all columns listed and the constant 10.

SELECTleast(UnitsInStock,ReorderLevel,10) FROMProductsWHEREReorderLevel > 0

LN

Returns the natural (base e) logarithm of the specified expression.

SELECTln(e())--returns 1

LOG

Returns the base 10 logarithm of the specified expression.

SELECTlog(10)--returns 1

PI

Returns the constant π

SELECTpi()--returnsπ

POW, POWER

Raises a number to the specified power.

SELECTpow(2,3)--returns 8

SELECTpower(3,2)--returns 9

ROUND

Rounds the input expression to the nearest integral value, and rounds midpoint values to the nearest even number.

SELECTround(4.4)--returns 4

SELECTround(4.5)--returns 4

SELECTround(4.6)--returns 5

SELECTround(5.5)--returns 6

ROUNDTO

Rounds the input expression to a specified number of fractional digits, and rounds midpoint values to the nearest even number.

SELECTroundto(3.44,1)--returns 3.4

SELECTroundto(3.45,1)--returns 3.4

SELECTroundto(3.46,1)--returns 3.5

SIGN

Returns an integer that indicates the sign of the specified input expression.

SELECTsign(2)--returns 1

SELECTsign(-2)--returns -1

SIN

Calculates the sine of the input expression.

SELECTsin(pi()/ 2)--returns 1

SQRT

Calculates the square root of the input expression.

SELECTsqrt(9)--returns 3

SQUARE

Calculates the square of the input expression.

SELECTsquare(5)--returns 25

TAN

Calculates the tangent of the input expression.

SELECTtan(pi()/ 4)--returns 1

Bit Manipulation Functions

Bit-testing and bit-manipulation functions.

BITTEST

Tests if the bit at the specified positi254on in the specified numeric expression is equal to 1. The syntax is:

bittest(numeric_expression_to_test, index_of_the_bit_to_test)

SELECTbittest(2,0)--returns false

SELECTbittest(2,1)--returns true

SETBIT

Sets the bit at the specified position in the specified numeric expression to the specified value. The syntax is:

setbit(numeric_expression, index_of_the_bit_to_set, value_of_the_bit)

SELECTsetbit(2,0,1)--returns 3

SELECTsetbit(2,1,0)--returns 0

SHL

Shifts the bits of the specified numeric expression to the left by the specified number of positions. The syntax is:

shl(numeric_expression, number_of_positions_by_which_to_shift_bits)

SELECTshl(1,3)--returns 8

SHR

Shifts the bits of the specified numeric expression to the right by the specified number of positions. The syntax is:

shr(numeric_expression, number_of_positions_by_which_to_shift_bits)

SELECTshr(8,3)--returns 1

TOGGLEBIT

Toggles the bit of the specified numeric expression at the specified index. The syntax is:

togglebit(numeric_expression, index_of_the_bit_to_toggle)

SELECTtogglebit(2,0)--returns 3

SELECTtogglebit(2,1)--returns 0

Conversion Functions

These functions provide type-conversion and formatting functionality.

CAST

Converts an expression of one data type to another. The syntax is:

cast(expressionASdata_type)

For the list of data types supported by AnalytiX-BI, refer to the data types section. The following query casts a double value to integer.

SELECTcast(1.232ASInt32)--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 section. Assuming that the data model’s culture is en-us, the following query convert the string 1.143 to a Double.

SELECTcast('1.143'ASDouble)--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 decimal separator) to a Double.

SELECTcast('1,143'COLLATEit-it_ciASDouble)--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.

SELECTcast('2021-09-28'ASDateTime)

SELECTcast('09/28/2021 1:41 PM'ASDateTime)

The following query converts an ISO 8601 date/time string with offset information to DateTimeOffset.

SELECTcast('2021-09-28T17:43:12.123-05'ASDateTimeOffset)

For more information on input strings, please refer to .NET’s DateTime.Parse documentation.

Converting strings to TimeSpan

When converting a string to a TimeSpan, the string must have 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.

SELECTcast('6'ASTimeSpan)--returns 6 days

SELECTcast('6:12'ASTimeSpan)--returns 6 hours, 12 minutes

SELECTcast('6:12:14'ASTimeSpan)--returns 6 hours, 12 minutes, 14 seconds

SELECTcast('1.6:12:14'ASTimeSpan)--returns 1 day, 6 hours, 12 minutes, 14 seconds

SELECTcast('6:12:14.123'ASTimeSpan)--returns 6 hours, 12 minutes, 14 seconds, 123 milliseconds

SELECTcast('6:12:14,123'COLLATEit-it_ciASTimeSpan)--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.

SELECTcast('true'ASBoolean)--returns true

SELECTcast('false'ASBoolean)--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.

SELECTcast(1.143ASString)--returns"1.143"

Note: Double or Single constants must always be specified using period as a decimal separator in AnalytiX-BI.

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 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, 0 milliseconds.

The following queries show a few examples of conversion to DateTime.

SELECTcast(0ASDateTime)--returns January 1, 1900, 12:00 AM

SELECTcast(1ASDateTime)--returns January 2, 1900, 12:00 AM

SELECTcast(2.25ASDateTime)--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.

SELECTcast(cast('1900-01-01'ASDateTime) ASInt32)--returns 0

SELECTcast(cast('1899-12-31'ASDateTime) ASInt32)--returns -1

SELECTcast(cast('1900-01-02'ASDateTime) ASInt32)--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 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 to conversion to TimeSpan.

SELECTcast(100ASTimeSpan)--returns 00:00:00.1000

SELECTcast(100.1ASTimeSpan)--returns 00:00:00.1001

SELECTcast(1000ASTimeSpan)--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 integer.

SELECTtotype(1.234,'int')--returns 1

Note: these functions are implemented for compatibility with the Expression Engine functions with the same name. In AnalytiX-BI queries use of the CAST function is recommended.

CONVERT

Converts an expression of one data type to another. The syntax is:

convert(expressionASdata_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.

SELECTconvert(UnitPriceASString,'c') FROMProducts

The following query formats the ProductID of products by adding leading zeros so that the result has always 4 digits.

SELECTconvert(ProductIDASString,'D4') FROMProducts

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

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.

SELECTtoboolean(0)--returns false

SELECTtoboolean('true')--returns true

SELECTtoboolean('1.1')--returns true in any culture

SELECTtoboolean('1,1')--fails because the conversion uses the invariant culture

SELECTtoboolean('1,1'COLLATEit-it_ci)--also fails because the conversion always uses the invariant culture, and COLLATE does not override it

Note: this function is implemented for compatibility with the Expression Engine function with the same name. In AnalytiX-BI queries use of the CAST function is recommended.

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.

SELECTtobooleanculture(0)--returns false

SELECTtobooleanculture('true')--returns true

SELECTtobooleanculture('1.1')--returns true in the en-us culture

SELECTtobooleanculture('1,1')--fails in the en-us culture

SELECTtobooleanculture('1,1'COLLATEit-it_ci)--returns true

Note: this function is implemented for compatibility with the Expression Engine function with the same name. In AnalytiX-BI queries use of the CAST function is recommended.

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.

SELECTtodatetime(0)--returns January 1, 1900 12:00 AM

Note: the Expression Engine does not allow converting numbers to DateTime, however since the internal implementation of TODATETIME in AnalytiX-BI uses the CAST function, this case is supported.

SELECTtodatetime('2021-09-29')--returns September 29, 2021 12:00 AM

SELECTtodatetime('09/29/2021')--returns September 29, 2021 12:00 AM

SELECTtodatetime('29/09/2021')--fails because the conversion uses the invariant culture

SELECTtodatetime('29/09/2021'COLLATEit-it_ci)--also fails because the conversion always uses the invariant culture, and COLLATE does not override it

Note: this function is implemented for compatibility with the Expression Engine function with the same name. In AnalytiX-BI queries use of the CAST function is recommended.

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.

SELECTtodatetimeculture(0)--returns January 1, 1900 12:00 AM

Note: the Expression Engine does not allow converting numbers to DateTime, however since the internal implementation of TODATETIMECULTURE in AnalytiX-BI uses the CAST function, this case is supported.

SELECTtodatetimeculture('2021-09-29')--returns September 29, 2021 12:00 AM

SELECTtodatetimeculture('09/29/2021')--returns September 29, 2021 12:00 AM in the en-us culture

SELECTtodatetimeculture('29/09/2021')--fails in the en-us culture

SELECTtodatetimeculture('29/09/2021'COLLATEit-it_ci)--returns September 29, 2021 12:00 AM

Note: this function is implemented for compatibility with the Expression Engine function with the same name. In AnalytiX-BI queries use of the CAST function is recommended.

TOFORMAT

Converts an expression to 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.

SELECTtoformat(UnitPrice,'c') FROMProducts--returns prices with the invariant culture currency symbol ¤

SELECTtoformat(ProductID,'D4') FROMProducts--returns product IDs as 4 digits with leading zeroes

Note: this function is implemented for compatibility with the Expression Engine function with the same name. In AnalytiX-BI queries use of the CONVERT function is recommended. Please also refer to the CONVERT function for more information on format strings.

TOFORMATCULTURE

Converts an expression to 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.

SELECTtoformatculture(UnitPrice,'c') FROMProducts--returns prices with the currency symbol $ in en-us culture

SELECTtoformatculture(ProductID,'D4') FROMProducts--returns product IDs as 4 digits with leading zeroes

TONUMBER

Converts an expression to 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.

SELECTtonumber('1')--returns 1 (double)

Note: the Expression Engine returns an Int64 for strings that represent integers, whereas AnalytiX-BI always returns Double.

SELECTtonumber('1.1')--returns 1.1 in any culture

SELECTtonumber('1,1')--fails because the conversion uses the invariant culture

SELECTtonumber('1,1'COLLATEit-it_ci)--also fails because the conversion always uses the invariant culture, and COLLATE does not override it

Note: this function is implemented for compatibility with the Expression Engine function with the same name. In AnalytiX-BI queries use of the CAST function is recommended.

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.

SELECTtonumberbase('11001',2)--returns 25 (from binary 11001)

SELECTtonumberbase('10',8)--returns 8 (from octal 10)

SELECTtonumberbase('18',10)--returns 18 (from decimal 18)

SELECTtonumberbase('18.1',10)--fails

Note: the Expression Engine can convert base-10 fractional values to Double, however this is not supported in AnalytiX-BI.

SELECTtonumberbase('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.

SELECTtonumberculture('1')--returns 1 (double)

Note: the Expression Engine returns an Int64 for strings that represent integers, whereas AnalytiX-BI always returns Double.

SELECTtonumberculture('1.1')--returns 1.1 in the en-us culture

SELECTtonumberculture('1,1')--fails in the en-us culture

SELECTtonumberculture('1,1'COLLATEit-it_ci)--returns 1.1

Note: this function is implemented for compatibility with the Expression Engine function with the same name. In AnalytiX-BI queries use of the CAST function is recommended.

TOSTRING

Converts an expression to 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.

SELECTtostring(false)--returns 'False'

SELECTtostring(1.234)--returns '1.234'

SELECTtostring(now())--returns the current date/time formatted with the invariant culture

Note: this function is implemented for compatibility with the Expression Engine function with the same name. In AnalytiX-BI queries use of the CAST function is recommended.

TOSTRINGCULTURE

Converts an expression to 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.

SELECTtostring(false)--returns 'False'

SELECTtostring(1.234)--returns '1.234' in a en-us culture

SELECTtostring(now())--returns the current date/time formatted with the data model’s culture

Note: this function is implemented for compatibility with the Expression Engine function with the same name. In AnalytiX-BI queries use of the CAST function is recommended.

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.

SELECTtotimespan(100)--returns 00:00:00.1000

SELECTtotimespan(100.1)--returns 00:00:00.1001

SELECTtotimespan(1000)--returns 00:00:01.0000

SELECTtotimespan('6:12:14.123')--returns 6 hours, 12 minutes, 14 seconds, 123 milliseconds

SELECTtotimespan('6:12:14,123')--fails because the conversion uses the invariant culture

Note: this function is implemented for compatibility with the Expression Engine function with the same name. In AnalytiX-BI queries use of the CAST function is recommended.

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.

SELECTtotimespanculture(100)--returns 00:00:00.1000

SELECTtotimespanculture(100.1)--returns 00:00:00.1001

SELECTtotimespanculture(1000)--returns 00:00:01.0000

SELECTtotimespanculture('6:12:14.123')--returns 6 hours, 12 minutes, 14 seconds, 123 milliseconds

SELECTtotimespanculture('6:12:14,123'COLLATEit-it_ci)--returns 6 hours, 12 minutes, 14 seconds, 123 milliseconds

Note: this function is implemented for compatibility with the Expression Engine function with the same name. In AnalytiX-BI queries use of the CAST function is recommended.

Date and Time Functions

These functions operate on DateTime values and provide related functionality.

ADDDAYS

Adds the specified number of days to a DateTime expression. The number of days can be negative, which will subtract the value from the DateTime expression.

SELECTadddays(cast('2021-09-29 18:32:45.123'ASDateTime), 1)--returns September 30, 2021 6:32:45.123 PM

ADDMONTHS

Adds the specified number of months to a DateTime expression. The number of months can be negative, which will subtract the value from the DateTime expression.

SELECTaddmonths(cast('2021-09-29 18:32:45.123'ASDateTime), 1)--returns October 29, 2021 6:32:45.123 PM

ADDYEARS

Adds the specified number of years to a DateTime expression. The number of years can be negative, which will subtract the value from the DateTime expression.

SELECTaddyears(cast('2021-09-29 18:32:45.123'ASDateTime), 1)--returns September 30, 2022 6:32:45.123 PM

BDAY

Returns a DateTime value where the time part of the specified DateTime expression is set to 12:00:00 AM. The returned DateTime value has its Kind property set to Unspecified.

SELECTbday(cast('2021-09-29 18:32:45.123'ASDateTime))--returns September 29, 2021 12:00:00.000 AM

BHOUR

Returns a DateTime value where the time part of the specified DateTime expression is set to the beginning of the hour. The returned DateTime value has its Kind property set to Unspecified.

SELECTbhour(cast('2021-09-29 18:32:45.123'ASDateTime))--returns September 29, 2021 6:00:00.000 PM

BMINUTE

Returns a DateTime value where the time part of the specified DateTime expression is set to the beginning of the minute. The returned DateTime value has its Kind property set to Unspecified.

SELECTbminute(cast('2021-09-29 18:32:45.123'ASDateTime))--returns September 29, 2021 6:32:00.000 PM

BMONTH

Returns a DateTime value where the day is set to the first day of the month relative to the specified DateTime expression and the time part is set to 12:00:00 AM. The returned DateTime value has its Kind property set to Unspecified.

SELECTbmonth(cast('2021-09-29 18:32:45.123'ASDateTime))--returns September 1, 2021 12:00:00.000 AM

BSECOND

Returns a DateTime value where the time part of the specified DateTime expression is set to the beginning of the second. The returned DateTime value has its Kind property set to Unspecified.

SELECTbsecond(cast('2021-09-29 18:32:45.123'ASDateTime))--returns September 29, 2021 6:32:45.000 PM

BWEEK

Returns a DateTime value where the day is set to the first day of the week relative to the specified DateTime expression and the time part is set to 12:00:00 AM. The returned DateTime value has its Kind property set to Unspecified.

The first day of the week is determined by the culture associated to the data model’s collation.

SELECTbweek(cast('2021-09-29 18:32:45.123'ASDateTime))--returns September 26, 2021 12:00:00.000 AM (Sunday) for the en-us culture

SELECTbweek(cast('2021-09-29 18:32:45.123'ASDateTime))--returns September 27, 2021 12:00:00.000 AM (Monday) for the it-it culture

BYEAR

Returns a DateTime value where the day is set to the first day of the year relative to the specified DateTime expression and the time part is set to 12:00:00 AM. The returned DateTime value has its Kind property set to Unspecified.

SELECTbyear(cast('2021-09-29 18:32:45.123'ASDateTime))--returns January 1, 2021 12:00:00.000 AM

DATEPART

Returns the specified part from the specified DateTime expression. The syntax is:

datepart(datetime_expression,'date_part')

The date_part parameter can be one of the following values (case-insensitive):

· year

· month

· day

· dayofweek

· dayofweekoffset

· hour

· minute

· second

· millisecond

The following queries show examples with the DATEPART function.

SELECTdatepart(cast('2021-09-29 18:32:45.123'ASDateTime),'year')--returns 2021

SELECTdatepart(cast('2021-09-29 18:32:45.123'ASDateTime),'month')--returns 9

SELECTdatepart(cast('2021-09-29 18:32:45.123'ASDateTime),'day')--returns 29

SELECTdatepart(cast('2021-09-29 18:32:45.123'ASDateTime),'dayofweek')--returns 3 (Wednesday); dayofweek is culture-invariant and returns a value between 0 (Sunday) to 6 (Saturday)

SELECTdatepart(cast('2021-09-29 18:32:45.123'ASDateTime),'dayofweekoffset')--returns a value relative to the first day of week in the culture of the data model’s collation

Note: see the DAYOFWEEKOFFSET function for more details

SELECTdatepart(cast('2021-09-29 18:32:45.123'ASDateTime),'hour')--returns 18

SELECTdatepart(cast('2021-09-29 18:32:45.123'ASDateTime),'minute')--returns 32

SELECTdatepart(cast('2021-09-29 18:32:45.123'ASDateTime),'second')--returns 45

SELECTdatepart(cast('2021-09-29 18:32:45.123'ASDateTime),'millisecond')--returns 123

DATETIMEFROMPARTS

Builds a DateTime value from individual parts, the returned DateTime value has its Kind property set to Unspecified. The function has three different overloads:

datetimefromparts(year_expression,month_expression,day_expression,hour_expression,minute_expression,second_expression,millisecond_expression)

datetimefromparts(year_expression,month_expression,day_expression,hour_expression,minute_expression,second_expression)

datetimefromparts(year_expression,month_expression,day_expression)

The following query shows how to use the DATETIMEFROMPARTS function to build a DateTime value.

SELECTdatetimefromparts(2021,09,29,18,32,45,123)--returns September 29, 2021 6:32:45.123 PM

DAY

Returns the day part of a DateTime expression. This function is a shortcut for DATEPART when the date_part parameter is day.

SELECTday(cast('2021-09-29 18:32:45.123'ASDateTime))--returns 29

DAYNAME

Returns the name of the day of a DateTime expression in the invariant culture. Invariant culture day names are the English Sunday, Monday, Tuesday, Wednesday, Thursday, Friday and Saturday.

SELECTdayname(cast('2021-09-29 18:32:45.123'ASDateTime))--returns'Wednesday'

DAYNAMECULTURE

Returns the name of the day of a DateTime expression in the culture of the data model’s collation.

SELECTdaynameculture(cast('2021-09-29 18:32:45.123'ASDateTime))--returns'Wednesday'in en-us culture

SELECTdaynameculture(cast('2021-09-29 18:32:45.123'ASDateTime))--returns'mercoledì'in it-it culture

DAYOFWEEK

Returns the numeric index of the day of a DateTime expression in the invariant culture. This function is a shortcut for DATEPART when the date_part parameter is dayofweek.

In the invariant culture the first day of the week is always Sunday with index 0, and the last day of the week is Saturday with index 6.

SELECTdayofweek(cast('2021-09-29 18:32:45.123'ASDateTime))--returns 3 (Wednesday)

DAYOFWEEKOFFSET

Returns the numeric index of the day of a DateTime expression in the culture of the data model’s collation. This function is a shortcut for DATEPART when the date_part parameter is dayofweekoffset.

The following query returns 3 as day of the week when it runs in the en-us culture, where the first day of the week is Sunday.

SELECTdayofweekoffset(cast('2021-09-29 18:32:45.123'ASDateTime))--returns 3 (Wednesday)

The same query returns 2 as day of the week when it runs in the it-it culture, where the first day of the week is Monday.

SELECTdayofweekoffset(cast('2021-09-29 18:32:45.123'ASDateTime))--returns 2 (Wednesday)

DAYSECONDS

Returns the number of seconds elapsed since the beginning of the day of a DateTime expression.

SELECTdayseconds(cast('2021-09-29 18:32:45.123'ASDateTime))--returns 66765

EMONTH

Returns a DateTime value where the day is set to the last day of the month relative to the specified DateTime expression and the time part is set to 12:00:00 AM. The returned DateTime value has its Kind property set to Unspecified.

SELECTemonth(cast('2021-09-29 18:32:45.123'ASDateTime))--returns September 30, 2021 12:00:00.000 AM

EWEEK

Returns a DateTime value where the day is set to the last day of the week relative to the specified DateTime expression and the time part is set to 12:00:00 AM. The returned DateTime value has its Kind property set to Unspecified.

The last day of the week is determined by the culture associated to the data model’s collation.

SELECTeweek(cast('2021-09-29 18:32:45.123'ASDateTime))--returns October 2, 2021 12:00:00.000 AM (Saturday) for the en-us culture

SELECTeweek(cast('2021-09-29 18:32:45.123'ASDateTime))--returns October 3, 2021 12:00:00.000 AM (Sunday) for the it-it culture

EYEAR

Returns a DateTime value where the day is set to the last day of the year relative to the specified DateTime expression and the time part is set to 12:00:00 AM.

SELECTeyear(cast('2021-09-29 18:32:45.123'ASDateTime))--returns December 31, 2021 12:00:00.000 AM

FROMEPOCH

Converts a Unix seconds epoch to a DateTime value. The returned DateTime value is in UTC time and has its Kind property set to Utc.

SELECTfromepoch(1632940365)--returns September 29, 2021 6:32:45 PM

FROMEPOCHOFFSET

Converts a Unix seconds epoch to a DateTimeOffset value.

SELECTfromepochoffset(1632940365)--returns September 29, 2021 6:32:45 PM GMT

GETDATE

This function has two overloads:

getdate()

Returns a DateTime value representing the current date and time. The result of this function is the same as the NOW function. The returned DateTime value has its Kind property set to Unspecified.

SELECTgetdate()--returns the current date and time

The second overload accepts a DateTime expression as parameter, and returns the date portion with the time set to 12:00:00 AM. The returned DateTime value has its Kind property set to Unspecified.

SELECTgetdate(cast('2021-09-29 18:32:45.123'ASDateTime))--returns September 29, 2021 12:00:00.000 AM

GETTIMEOFDAY

Returns the time portion of a DateTime expression as a TimeSpan.

SELECTgettimeofday(cast('2021-09-29 18:32:45.123'ASDateTime))--returns 0.18:32:45.123

GETUTCDATE

Returns a DateTime value representing the current date and time. The result of this function is the same as the UTCNOW function. The returned DateTime value has its Kind property set to Utc.

SELECTgetutcdate()--returns the current date and time in UTC

HOUR

Returns the hour part of a DateTime expression. This function is a shortcut for DATEPART when the date_part parameter is hour.

SELECThour(cast('2021-09-29 18:32:45.123'ASDateTime))--returns 18

ISDST

Returns whether a DateTime expression represents a point in time within a Daylight Savings Time period or not. If the Kind property of the DateTime expression is Unspecified or Local, the value will be considered a local time – where “local” is the time zone configured for the machine. If the Kind property is Utc, the ISDST function will return false.

SELECTisdst(cast('2021-09-29 18:32:45.123'ASDateTime))--returns true for Eastern Time (US & Canada)

SELECTisdst(cast('2021-02-20 18:32:45.123'ASDateTime))--returns false for Eastern Time (US & Canada)

SELECTisdst(utcnow())--always returns false

MAXTIME

Returns the maximum value for DateTime expressions.

SELECTmaxtime()-- returns 12/31/9999 11:59:59 PM

MILLISECOND

Returns the millisecond part of a DateTime expression. This function is a shortcut for DATEPART when the date_part parameter is millisecond.

SELECTmillisecond(cast('2021-09-29 18:32:45.123'ASDateTime))--returns 123

MINTIME

Returns the minimum value for DateTime expressions. Note that DateTime expressions are allowed to assume a value lesser than MINTIME, however MINTIME produces the exact DateTime value that, when converted to a numeric type, returns zero.

SELECTcast(mintime() ASInt32)--returns 0

MINUTE

Returns the minute part of a DateTime expression. This function is a shortcut for DATEPART when the date_part parameter is minute.

SELECTminute(cast('2021-09-29 18:32:45.123'ASDateTime))--returns 32

MONTH

Returns the month part of a DateTime expression. This function is a shortcut for DATEPART when the date_part parameter is month.

SELECTmonth(cast('2021-09-29 18:32:45.123'ASDateTime))--returns 9

MONTHNAME

Returns the name of the month of a DateTime expression in the invariant culture. Invariant culture day names are the English January, February, March, April, May, June, July, August, September, October, November and December.

SELECTmonthname(cast('2021-09-29 18:32:45.123'ASDateTime))--returns'September'

MONTHNAMECULTURE

Returns the name of the month of a DateTime expression in the culture of the data model’s collation.

SELECTmonthnameculture(cast('2021-09-29 18:32:45.123'ASDateTime))--returns'September'in en-us culture

SELECTmonthnameculture(cast('2021-09-29 18:32:45.123'ASDateTime))--returns'settembre'in it-it culture

NOON

Returns the date portion with the time set to 12:00:00 PM. The returned DateTime value has its Kind property set to Unspecified.

SELECTnoon(cast('2021-09-29 18:32:45.123'ASDateTime))--returns September 29, 2021 12:00:00.000 PM

NOW

Returns the current date and time. The returned DateTime value has its Kind property set to Unspecified.

SELECTnow()--returns the current date and time

SECOND

Returns the second part of a DateTime expression. This function is a shortcut for DATEPART when the date_part parameter is second.

SELECTsecond(cast('2021-09-29 18:32:45.123'ASDateTime))--returns 45

TODAY

Returns the current date with the time set to 12:00:00 AM. The returned DateTime value has its Kind property set to Unspecified.

SELECTtoday()--returns the current date with the time portion set to 12:00:00 AM

TOEPOCH

Converts a DateTime expression into a Unix epoch in seconds. If the DateTime expression has its Kind property set to Unspecified it will be considered as local time, and converted to UTC before getting converted to epoch.

SELECTtoepoch(cast('2021-09-29T18:32:45Z'ASDateTime))--CAST returns a DateTime with Kind set to Utc, so returns 1632940365

SELECTtoepoch(cast('2021-09-29T18:32:45'ASDateTime))--CAST returns a DateTime with Kind set to Unspecified, which is interpreted as local time, so returns 1632954765 (representing September 29, 2021 14:32:45 UTC)

Note: the behavior of this function is different from the equivalent function in the Expression Engine: in the Expression Engine, the DateTime’s Kind property is ignored and the value always assumed to be UTC.

TOLOCAL

Converts a DateTime expression to local time and returns a DateTime value with its Kind property set to Local.

The result of the conversion is dependent on the Kind property of the expression.

· Utc. The expression is converted to local time.

· Local. No conversion is performed.

· Unspecified. The expression is assumed to be UTC and the conversion is performed as if Kind were Utc.

selecttolocal(cast('2021-09-29T18:32:45Z'ASDateTime))--returns September 29, 2021 18:32:45 UTC in local time

TOTIMEZONETIME

Converts a DateTime expression from one time zone to another. The function has two overloads:

totimezonetime(datetime_expression, destination_time_zone)

totimezonetime(datetime_expression, destination_time_zone, source_time_zone)

When used without the source_time_zone parameter, the datetime_expression’s Kind property determines the source time zone. Both source_time_zone and destination_time_zone are case-insensitive strings, and can be either a time zone identifier or a fixed time offset.

To retrieve the identifier for a specific time zone, it is possible to use the Time Zone picker for Equipment in the Assets provider in Workbench.

When the input DateTime expression’s Kind property is set to Local or Unspecified, it will be considered local time.

SELECTtotimezonetime('2021-09-29T12:00:00','Eastern Standard Time')--returns September 29, 2021 12:00:00 PM when run in Eastern Time (US & Canada)

SELECTtotimezonetime('2021-09-29T12:00:00','Pacific Standard Time')--returns September 29, 2021 9:00:00 AM when run in Eastern Time (US & Canada)

When the input DateTime expression’s Kind property is set to Utc, it will be considered UTC time.

SELECTtotimezonetime('2021-09-29T12:00:00Z','Eastern Standard Time')--returns September 29, 2021 8:00:00 AM in any time zone

When the input DateTime expression’s Kind property is set to Utc the source_time_zone parameter will be ignored even if specified. The following query returns September 29, 2021 8:00:00 AM even if the source time zone is set to Eastern Standard Time because the input is a UTC DateTime.

SELECTtotimezonetime('2021-09-29T12:00:00Z','Eastern Standard Time','Eastern Standard Time')--returns September 29, 2021 8:00:00 AM in any time zone

When the input DateTime expression’s Kind property is set to Local or Unspecified, it will be interpreted as a local time in the source time zone. The following query translates a DateTime value from Pacific Standard Time to Eastern Standard Time.

SELECTtotimezonetime('2021-09-29T12:00:00','Eastern Standard Time','Pacific Standard Time')--returns September 29, 2021 3:00:00 PM

Conversions take into account daylight savings status. The following query shows a 6 hours difference between Eastern Time and Central European Time when daylight savings was still in effect in both time zones.

SELECTtotimezonetime('2020-10-24T12:00:00','Central Europe Standard Time','Eastern Standard Time')--returns October 24, 2020 6:00:00 PM (6 hours difference)

The following query shows a 5 hours difference because daylight savings ended in Central European Time, but not in Eastern Time.

SELECTtotimezonetime('2020-10-25T12:00:00','Central Europe Standard Time','Eastern Standard Time')--returns October 25, 2020 5:00:00 PM (5 hours difference)

TOUTC

Converts a DateTime expression to Universal Coordinated Time and returns a DateTime value with its Kind property set to Utc.

The result of the conversion is dependent on the Kind property of the expression.

· Utc. No conversion is performed.

· Local. The expression is converted to UTC.

· Unspecified. The expression is assumed to be local time and the conversion is performed as if Kind were Local.

selecttoutc(cast('2021-09-29T18:32:45'ASDateTime))--returns September 29, 2021 18:32:45 local time in UTC

UTCNOW

Returns the current UTC date and time. The returned DateTime value has its Kind property set to Utc.

SELECTutcnow()--returns the current UTC date and time

WEEKDAY

Returns the numeric index of the day of a DateTime expression in the invariant culture. In the invariant culture the first day of the week is always Sunday with index 0, and the last day of the week is Saturday with index 6.

SELECTweekday(cast('2021-09-29 18:32:45.123'ASDateTime))--returns 3 (Wednesday)

This function returns the same result as DAYOFWEEK.

WEEKNUMBER

Returns a number between 1 and 53 representing the week in the year of the given date in the Gregorian calendar. The function has three overloads:

weeknumber(datetime_expression)

weeknumber(datetime_expression, week_rule)

weeknumber(datetime_expression, week_rule, firstDayofWeek)

The week_rule parameter is an enumerated integer that can assume the values described in the following list. The default value is 0 (FirstDay).

· 0 (FirstDay). Indicates that the first week of the year starts on the first day of the year and ends before the following designated first day of the week.

· 1 (FirstFullWeek). Indicates that the first week of the year begins on the first occurrence of the designated first day of the week.

· 2 (FirstFourDayWeek). Indicates that the first week of the year is the first week with four or more days before the designated first day of the week.

The firstDayofWeek parameter indicates what the first day in the week is. The default value is 0 (Sunday).

SELECTweeknumber(cast('2021-09-29 18:32:45.123'ASDateTime))--returns 40

SELECTweeknumber(cast('2021-09-29 18:32:45.123'ASDateTime),1)--returns 39

SELECTweeknumber(cast('2021-09-29 18:32:45.123'ASDateTime),1,4)--returns 38

SELECTweeknumber(cast('2021-09-29 18:32:45.123'ASDateTime),2)--returns 39

WEEKNUMBERCULTURE

Returns a number between 1 and 53 representing the week in the year of the given date in the default calendar in the culture of the data model’s collation. The function has three overloads:

weeknumberculture(datetime_expression)

weeknumberculture(datetime_expression, week_rule)

weeknumberculture(datetime_expression, week_rule, firstDayofWeek)

The week_rule parameter is an enumerated integer that can assume the values described in the following list. The default value is 0 (FirstDay).

· 0 (FirstDay). Indicates that the first week of the year starts on the first day of the year and ends before the following designated first day of the week.

· 1 (FirstFullWeek). Indicates that the first week of the year begins on the first occurrence of the designated first day of the week.

· 2 (FirstFourDayWeek). Indicates that the first week of the year is the first week with four or more days before the designated first day of the week.

The firstDayofWeek parameter indicates what the first day in the week is. The default value is 0 (Sunday).

SELECTweeknumberculture(cast('2021-09-29 18:32:45.123'ASDateTime))--returns 40

SELECTweeknumberculture(cast('2021-09-29 18:32:45.123'ASDateTime),1)--returns 39

SELECTweeknumberculture(cast('2021-09-29 18:32:45.123'ASDateTime),1,4)--returns 38

SELECTweeknumberculture(cast('2021-09-29 18:32:45.123'ASDateTime),2)--returns 39

YDAY

Returns the date of the day before the current date, with its time part set to 12:00:00 AM. The returned value has its Kind property set to Unspecified.

SELECTyday()--returns yesterday's date at 12:00:00 AM

YEAR

Returns the year part of a DateTime expression. This function is a shortcut for DATEPART when the date_part parameter is year.

SELECTyear(cast('2021-09-29 18:32:45.123'ASDateTime))--returns 2021

YEARDAY

Returns the day of the year of a DateTime expression. The result is a number between 1 and 366.

SELECTyearday(cast('2021-09-29 18:32:45.123'ASDateTime))--returns 272

Quality Functions

These functions operate on Unit32 Status Codes, typically returned by an operation on real-time or historical values.

GETDESCQ

Returns a human-readable string of the UInt32 expression representing a Status Code.

SELECTgetdescq(2147483658)--returns "Bad - User Access Denied"

ISBADQ

Determines if the specified UInt32 expression represents a Bad Status Code.

SELECTisbadq(0)--returns false

SELECTisbadq(134217728)--returns true

ISGOODQ

Determines if the specified UInt32 expression represents a Good Status Code.

SELECTisgoodq(0)--returns true

SELECTisgoodq(134217728)--returns false

ISUNCQ

Determines if the specified UInt32 expression represents an Uncertain Status Code.

SELECTisuncq(134217728)--returns false

SELECTisuncq(3221225472)--returns true

String Functions

These functions operate on strings and provide related functionality.

ASCIITOCHAR

Converts an Int64 expression representing ASCII values(s) to the equivalent character string. Each byte in the Int64 expression represents one character. As the input of this function is a 64-bit integer, the resulting string is limited to 8 characters.

SELECTasciitochar(65)--returns A

SELECTasciitochar(4276803)--returns ABC (0x414243)

ASCIITOWCHAR

Converts an Int64 expression representing Unicode values(s) to the equivalent Unicode character string. Each 2 bytes in the Int64 expression represent one Unicode character. As the input of this function is a 64-bit integer, the resulting string is limited to 4 characters.

SELECTasciitowchar(8721)--returns∑

SELECTasciitowchar(1735478956)--returns東京

BASE64DECODE

Returns the Base64 encoded version of the input String expression.

SELECTbase64encode('hello')--returns aGVsbG8=

BASE64ENCODE

Decodes the input Base64 encoded String expression.

SELECTbase64decode('aGVsbG8=')--returns hello

CHARTOASCII

Converts a String expression of one or more characters into a Int64 value representing the corresponding ASCII values. Each character in the input String expression represents one byte in the resulting Int64 value. As the output of this function is a Int64 value, the input expression is limited to 8 characters.

SELECTchartoascii('A')--returns 65

SELECTchartoascii('ABC')--returns 4276803

COMPARE

Compares two String expressions using the invariant culture, either with case sensitivity or case insensitivity. The syntax is:

compare(first_string_expression, second_string_expression, bool_case_sensitive_comparison)

The result is -1 if the first string is lesser than the second string, 1 if the first string is greater than the second string and 0 if the two strings are equal.

SELECTcompare('a','A', true)--returns -1 (case-sensitive)

SELECTcompare('A','a', true)--returns 1 (case-sensitive)

SELECTcompare('a','a', true)--returns 0 (case-sensitive)

SELECTcompare('a','A', false)--returns 0 (case-insensitive)

SELECTcompare('diyarbakır','DİYARBAKIR', false)--returns -1 (case-insensitive, but because of the Turkish i, the first string islesser than the second)

COMPARECULTURE

Compares two String expressions using the culture of the collation of the first string, either with case sensitivity or case insensitivity. If the COLLATE keyword is not specified, the culture of the data model’s collation will be used. The syntax is:

compareculture(first_string_expression, second_string_expression, bool_case_sensitive_comparison)

The result is -1 if the first string is lesser than the second string, 1 if the first string is greater than the second string and 0 if the two strings are equal.

SELECTcompareculture('a','A', true)--returns -1 (case-sensitive)

SELECTcompareculture('A','a', true)--returns 1 (case-sensitive)

SELECTcompareculture('a','a', true)--returns 0 (case-sensitive)

SELECTcompareculture('a','A', false)--returns 0 (case-insensitive)

SELECTcompareculture('diyarbakır'COLLATEtr-tr_ci,'DİYARBAKIR', false)--returns 0 (case-insensitive, but since the comparison is executed in the Turkish culture, the two strings result equal)

CONCAT

Takes N String expressions and produces a single String value representing the concatenation of the input expressions.

SELECTconcat('a','b','c')--returns "abc"

CONCAT_WS

Takes a String expression representing a separator and N String expressions to produce the concatenation of the N expressions separated by the specified separator.

Note: this function is the equivalent of the Expression Engine’s join function, however the JOIN keyword is reserved for joins in AnalytiX-BI.

SELECTconcat_ws('/','a','b','c')--returns "a/b/c"

INDEXOF

Returns the zero-based index of the first occurrence of a String expression inside another String expression, or -1 if the occurrence was not found. The search is case-sensitive. The function has three overloads:

indexof(string_expression_where_to_search, string_value_expression_to_find)

indexof(string_expression_where_to_search, string_value_expression_to_find, index_where_to_start_search)

indexof(string_expression_where_to_search, string_value_expression_to_find, index_where_to_start_search, number_of_characters_to_examine)

The string_value_expression_to_find parameter can be a single character or a multi-character string.

SELECTindexof('Condiments/Confections','o')--returns 1

SELECTindexof('Condiments/Confections','o',2)--returns 12

SELECTindexof('Condiments/Confections','o',2,10)--returns -1

SELECTindexof('Condiments/Confections','dime',2,10)--returns 3

INDEXOFANY

Returns the zero-based index of the first occurrence within a String expression of any character from another String expression, or -1 if no occurrence was not found. The search is case-sensitive. The function has three overloads:

indexofany(string_expression_where_to_search, set_of_characters_to_search_for)

indexofany(string_expression_where_to_search, set_of_characters_to_search_for, index_where_to_start_search)

indexofany(string_expression_where_to_search, set_of_characters_to_search_for, index_where_to_start_search, number_of_characters_to_examine)

When the set_of_characters_to_search_for parameter is specified as a String, each character in the string will be checked as an individual character.

SELECTindexofany('Condiments/Confections','no')--returns 1

SELECTindexofany('Condiments/Confections','no',2)--returns 2

SELECTindexofany('Condiments/Confections','no',3,7)--returns 7

INDEXOFOCCURENCE

Returns the zero-based index of the N-th occurrence of a String expression inside another String expression, or -1 if the occurrence was not found. The search is case-sensitive. The function has three overloads:

indexofoccurrence(string_expression_where_to_search, string_value_expression_to_find, nth_occurrence_to_find)

indexofoccurrence(string_expression_where_to_search, string_value_expression_to_find, nth_occurrence_to_find, index_where_to_start_search)

indexofoccurrence(string_expression_where_to_search, string_value_expression_to_find, nth_occurrence_to_find, index_where_to_start_search, number_of_characters_to_examine)

SELECTindexofoccurrence('Condiments/Confections','on',1)--returns 1

SELECTindexofoccurrence('Condiments/Confections','on',2)--returns 12

SELECTindexofoccurrence('Condiments/Confections','on',3)--returns 19

SELECTindexofoccurrence('Condiments/Confections','on',1, 2)--returns 12

SELECTindexofoccurrence('Condiments/Confections','on',1, 2, 10)--returns -1

INVERTCASE

Inverts the case of a String expression.

SELECTinvertcase('aBc')--returns "AbC"

LASTINDEXOF

Returns the zero-based index of the last occurrence of a String expression inside another String expression, or -1 if the occurrence was not found. The search is case-sensitive. The function has three overloads:

indexof(string_expression_where_to_search, string_value_expression_to_find)

indexof(string_expression_where_to_search, string_value_expression_to_find, index_where_to_start_search)

indexof(string_expression_where_to_search, string_value_expression_to_find, index_where_to_start_search, number_of_characters_to_examine)

The string_value_expression_to_find parameter can be a single character or a multi-character string. When the index_where_to_start_search parameter is specified, the search starts at that character backwards for number_of_characters_to_examine characters – or to the beginning of the string if the parameter is not specified.

SELECTlastindexof('Condiments/Confections','o')--returns 19

SELECTlastindexof('Condiments/Confections','o',18)--returns 12

SELECTlastindexof('Condiments/Confections','o',18,5)--returns -1

LASTINDEXOFANY

Returns the zero-based index of the last occurrence within a String expression of any character from another String expression, or -1 if no occurrence was not found. The search is case-sensitive. The function has three overloads:

lastindexofany(string_expression_where_to_search, set_of_characters_to_search_for)

lastindexofany(string_expression_where_to_search, set_of_characters_to_search_for, index_where_to_start_search)

lastindexofany(string_expression_where_to_search, set_of_characters_to_search_for, index_where_to_start_search, number_of_characters_to_examine)

When the set_of_characters_to_search_for parameter is specified as a String, each character in the string will be checked as an individual character. When the index_where_to_start_search parameter is specified, the search starts at that character backwards for number_of_characters_to_examine characters – or to the beginning of the string if the parameter is not specified.

SELECTlastindexofany('Condiments/Confections','no')--returns 20

SELECTlastindexofany('Condiments/Confections','no',18)--returns 13

SELECTlastindexofany('Condiments/Confections','no',10,10)--returns 7

LEFT

Returns the specified number of characters from the left side of a String Expression.

SELECT left('Category',3)--returns "Cat"

LEN

Returns the length (number of characters) of a String expression.

SELECTlen('Category')--returns 8

LIKE

Performs a wildcard string search on a String expression. The syntax is:

like(string_expression_where_to_search, pattern_to_search_for, bool_case_sensitive_comparison)

The pattern_to_search_for parameter may contain wildcards. Wildcard characters include:

· ? (question mark). Matches any single character.

· * (asterisk). Matches any character zero or more times.

· # (pound). Matches any single digit (0-9).

· [charlist]. Matches any single character in charlist

· [!charlist]. Matches any single character that is not in charlist

To match a literal wildcard, enclose it in square brackets, i.e.: [*].

SELECT like('Category','cat*', false)--returns true

SELECT like('Category','cat*', true)--returns false

SELECT like('Category','c?t*', false), like('Cotton','c?t*', false)--both return true

SELECT like('12','1#', false)--returns true for all two digit numbers starting with 1

SELECT like('cat','c[ao]t', false), like('cot','c[ao]t', false), like('cut','c[ao]t', false)--matches "cat", "cot" but not "cut"

SELECT like('cat','c[!a]t', false)--matches any three-letter word starting with "c" and ending with "t" but "cat"

SELECT like('hello?','*[?]', false)--matches any word ending in question mark

Note: this function is available for compatibility with the Expression Engine however, in AnalytiX-BI, it is recommended to use the LIKE operator.

LOWER

Makes a String expression lowercase. If the COLLATE keyword is not specified, the conversion is performed using the casing rules of the culture of the data model’s collation. This function produces the same result as the TOLOWER function.

SELECTlower('DİYARBAKIR')--returns "diyarbakir" in the en-us culture

SELECTlower('DİYARBAKIR'COLLATEtr-tr_ci)--returns "diyarbakır"

NCHAR

Returns the Unicode character with the specified integer code, as defined by the Unicode standard. This function is similar to the ASCIITOWCHAR function, but it only operates on a single character.

SELECTnchar(8721)--returns ∑

PATINDEX

Returns the starting position of the first occurrence of a pattern in a specified String expression, or 0 if the pattern is not found. If the COLLATE keyword is not specified, the culture of the data model’s collation will be used for the comparisons.

The index result of this function is 1-based, meaning that a match at the first character will return 1 instead of 0, unlike other functions like INDEXOF. The syntax is:

patindex(pattern_to_search_for, string_expression_where_to_search)

The pattern_to_search_for parameter works the same way as the LIKE operator, and accepts the same patterns, with the difference that the % character must come before and follow the pattern (except when searching for first or last characters).

SELECTpatindex('%t_g%','Category')--returns 3

The following example uses the ^ character to match the first character that is not a letter, digit or space.

SELECTpatindex('%[^ 0-9A-z]%','Welcome!')--returns 8

The following examples show the usage of PATINDEX with the COLLATE keyword.

SELECTpatindex('%I%','kır'COLLATEen-us_ci)--returns 0

SELECTpatindex('%I%','kır'COLLATEtr-tr_ci)--returns 2

RGMATCH

Returns whether the regular expression matches the input expression. This function has three overloads:

rgmatch(string_expression_where_to_search, regex_pattern_to_search_for)

rgmatch(string_expression_where_to_search, regex_pattern_to_search_for, index_where_to_start_search)

rgmatch(string_expression_where_to_search, regex_pattern_to_search_for, index_where_to_start_search, regex_options)

When the regex_options parameter is specified, it must be a combination of the following values:

· rg_culture_inv() Makes comparisons use the invariant culture.

· rg_ignore_case() Specifies case-insensitive matching.

· rg_ignore_pattern_ws() Eliminates unescaped white space from the pattern.

The following examples use a regular expression to match ZIP codes with 5 digits or 5+4 digits.

SELECTrgmatch('02035','^\\d{5}(-\\d{4})?$')--returns true

SELECTrgmatch('02035-0000','^\\d{5}(-\\d{4})?$')--returns true

The following examples show the usage of the start index.

SELECTrgmatch('Condiments','Con.*',0)--returns true

SELECTrgmatch('Condiments','Con.*',1)--returns false

The following examples show the usage of the options.

SELECTrgmatch('Condiments','con.*',0)--returns false

SELECTrgmatch('Condiments','con.*',0,rg_ignore_case())--returns true

SELECTrgmatch('Condiments',' con.*',0,rg_ignore_case()+ rg_ignore_pattern_ws())--returns true

RGREPLACE

Replaces all the instances of the string that match a regular expression pattern in a String expression with the specified replacement string. This function has two overloads:

rgreplace(string_expression_where_to_search, regex_pattern_to_search_for, string_expression_replacement_string)

rgreplace(string_expression_where_to_search, regex_pattern_to_search_for, string_expression_replacement_string, regex_options)

The following examples show usage of the RGREPLACE function.

SELECTrgreplace('--abc--adc--','a[a-z]c','xyz')--returns "--xyz--xyz---"

SELECTrgreplace('--abc--aDc--','a[a-z]c','xyz')--returns "--xyz--aDc---"

SELECTrgreplace('--abc--aDc--','a[a-z]c','xyz',rg_ignore_case())--returns "--xyz--xyz---"

REPLACE

Replaces all the instances of the string that match the specified search String expression in a String expression with the replacement String expression. The function has the following syntax:

replace(string_expression_where_to_search, string_expression_to_search_for, string_expression_replacement_string)

SELECTreplace('--abc--abc--adc--','abc','xyz')--returns "--xyz--xyz--adc--"

REVERSE

Reverses a string.

SELECTreverse('tab')--returns "bat"

RIGHT

Returns the specified number of characters from the right side of a String Expression.

SELECT right('Category',3)--returns "ory"

SUBSTRING

Extracts a portion of a String expression. The function has two overloads:

substring(string_expression_input, index_where_to_start_extraction)

substring(string_expression_input, index_where_to_start_extraction, number_of_characters_to_extract)

When the number_of_characters_to_extract parameter is not specified, the substring starts from index_where_to_start_extraction and ends at the end of string_expression_input. If index_where_to_start_extraction goes over the length of string_expression_input, an empty string is returned.

SELECTsubstring('hello world',6)--returns "world"

SELECTsubstring('hello world',3,5)--returns "lo wo"

SELECTsubstring('hello world',12)--returns an empty string

TOLOWER

SELECT tolower('DİYARBAKIR')--returns "diyarbakir" in the en-us culture

SELECT tolower('DİYARBAKIR'COLLATEtr-tr_ci)--returns "diyarbakır"

TOUPPER

Makes a String expression uppercase. If the COLLATE keyword is not specified, the conversion is performed using the casing rules of the culture of the data model’s collation. This function produces the same result as the UPPER function.

SELECTtoupper('diyarbakır')--returns "DIYARBAKIR"

SELECTtoupper('diyarbakır'COLLATEtr-tr_ci)--returns "DİYARBAKIR"

TRIM

Removes all leading and trailing occurrences of the specified set of characters from an input String expression. The function has two overloads:

trim(string_expression_to_trim)

trim(string_expression_to_trim, characters_to_trim)

If the characters_to_trim parameter is not specified, all leading and trailing spaces will be removed from string_expression_to_trim. Otherwise, each character specified in characters_to_trim will be removed from string_expression_to_trim if it is either leading or trailing.

SELECT'a'+ trim(' b ')+'c'--returns "abc"

SELECT'a'+ trim('#b$','#$')+'c'--returns "abc"

TRIMLEFT

Removes all leading occurrences of the specified set of characters from an input String expression. The function has two overloads:

trimleft(string_expression_to_trim)

trimleft(string_expression_to_trim, characters_to_trim)

If the characters_to_trim parameter is not specified, all leading spaces will be removed from string_expression_to_trim. Otherwise, each character specified in characters_to_trim will be removed from string_expression_to_trim if it is leading.

SELECT'a'+ trimleft(' b ')+'c'--returns "ab c"

SELECT'a'+ trimleft('#b$','#$')+'c'--returns "ab$c"

TRIMRIGHT

Removes all trailing occurrences of the specified set of characters from an input String expression. The function has two overloads:

trimright(string_expression_to_trim)

trimright(string_expression_to_trim, characters_to_trim)

If the characters_to_trim parameter is not specified, all trailing spaces will be removed from string_expression_to_trim. Otherwise, each character specified in characters_to_trim will be removed from string_expression_to_trim if it is trailing.

SELECT'a'+ trimright(' b ')+'c'--returns "a bc"

SELECT'a'+ trimright('#b$','#$')+'c'--returns "a#bc"

UPPER

SELECTupper('diyarbakır')--returns "DIYARBAKIR"

SELECTupper('diyarbakır'COLLATEtr-tr_ci)--returns "DİYARBAKIR"

URLDECODE

Encodes a URL String expression.

SELECTurlencode('a parameter value')--returns "a+parameter+value"

URLENCODE

Decodes a URL String expression.

SELECTurldecode('a+parameter+value')--returns "a parameter value"

WCHARTOASCII

Converts a String expression of one or more characters into a Int64 value representing the corresponding Unicode values. Each character in the input String expression represents two bytes in the resulting Int64 value. As the output of this function is a Int64 value, the input expression is limited to 4 characters.

SELECTwchartoascii('∑')--returns 8721

SELECTwchartoascii('東京')--returns 1735478956

TimeSpan Functions

These functions are for creating instances of TimeSpan and extracting values form them.

FROMDAYS

Returns a TimeSpan value from a Double expression that represents a number of days, either integral or fractional.

SELECTfromdays(1)-- returns 1.00:00:00

SELECTfromdays(1.5)-- returns 1.12:00:00

FROMHOURS

Returns a TimeSpan value from a Double expression that represents a number of hours, either integral or fractional.

SELECTfromhours(1)-- returns 01:00:00

SELECTfromhours(1.5)-- returns 01:30:00

FROMMILLISECONDS

Returns a TimeSpan value from a Double expression that represents a number of milliseconds, either integral or fractional.

SELECTfrommilliseconds(1)-- returns 00:00:00.0010000

SELECTfrommilliseconds(1.5)-- returns 00:00:00.0015000

FROMMINUTES

Returns a TimeSpan value from a Double expression that represents a number of minutes, either integral or fractional.

SELECTfromminutes(1)-- returns 00:01:00

SELECTfromminutes(1.5)-- returns 00:01:30

FROMSECONDS

Returns a TimeSpan value from a Double expression that represents a number of seconds, either integral or fractional.

SELECTfromseconds(1)-- returns 00:00:01

SELECTfromseconds(1.5)-- returns 00:00:01.5000000

TOTALDAYS

Returns a Double value representing the total number of days in a TimeSpan expression.

SELECTtotaldays('1.00:00:00')--returns 1

SELECTtotaldays('1.12:00:00')--returns 1.5

TOTALHOURS

Returns a Double value representing the total number of hours in a TimeSpan expression.

SELECTtotalhours('01:00:00')--returns 1

SELECTtotalhours('01:30:00')--returns 1.5

TOTALMILLISECONDS

Returns a Double value representing the total number of milliseconds in a TimeSpan expression.

SELECTtotalmilliseconds('00:00:01')--returns 1000

SELECTtotalmilliseconds('00:00:00.0015')--returns 1.5

TOTALMINUTES

Returns a Double value representing the total number of minutes in a TimeSpan expression.

SELECTtotalminutes('00:01:00')--returns 1

SELECTtotalminutes('00:01:30')--returns 1.5

TOTALSECONDS

Returns a Double value representing the total number of seconds in a TimeSpan expression.

SELECTtotalseconds('00:00:01')--returns 1

SELECTtotalseconds('00:00:01.500')--returns 1.5

Type Checking Functions

These function check if an expression can is of – or can be converted to – as specific data type.

ISBOOLEAN

Returns true if the specified expression can be converted to a Boolean, otherwise false. If the input expression is a string, possible conversion to Boolean will be checked using the invariant culture.

SELECTisboolean(false)--returns true

SELECTisboolean(1)--returns true

SELECTisboolean('a')--returns false

ISBOOLEANCULTURE

Returns true if the specified expression can be converted to a Boolean, otherwise false. If the input expression is a string and the COLLATE keyword is not specified, the conversion is checked using the culture of the data model’s collation.

SELECTisbooleanculture(false)--returns true

SELECTisbooleanculture(1)--returns true

SELECTisbooleanculture('1,1')--returns false in the en-us culture

SELECTisbooleanculture('1,1'COLLATEit-it_ci)--returns true

ISDATETIME

Returns true if the specified expression can be converted to a DateTime, otherwise false. If the input expression is a string, possible conversion to DateTime will be checked using the invariant culture.

SELECTisdatetime(now())--returns true

SELECTisdatetime(1)--returns true

SELECTisdatetime('09/29/2021')--returns true

SELECTisdatetime('29/09/2021')--returns false

ISDATETIMECULTURE

Returns true if the specified expression can be converted to a DateTime, otherwise false. If the input expression is a string and the COLLATE keyword is not specified, the conversion is checked using the culture of the data model’s collation.

SELECTisdatetimeculture(now())--returns true

SELECTisdatetimeculture(1)--returns true

SELECTisdatetimeculture('09/29/2021')--returns true

SELECTisdatetimeculture('29/09/2021')--returns false in the en-us culture

SELECTisdatetimeculture('29/09/2021'COLLATEit-it_ci)--returns true

ISNUMBER

Returns true if the specified expression can be converted to a Double, otherwise false. If the input expression is a string, possible conversion to Double will be checked using the invariant culture.

SELECTisnumber(1)--returns true

SELECTisnumber(1.1)--returns true

SELECTisnumber(now())--returns true

SELECTisnumber(CAST('00:10:00'ASTimeSpan))--returns true

SELECTisnumber('1.1')--returns true

SELECTisnumber('1,1')--returns false

ISNUMBERCULTURE

Returns true if the specified expression can be converted to a Double, otherwise false. If the input expression is a string and the COLLATE keyword is not specified, the conversion is checked using the culture of the data model’s collation.

SELECTisnumberculture(1)--returns true

SELECTisnumberculture(1.1)--returns true

SELECTisnumberculture(now())--returns true

SELECTisnumberculture(CAST('00:10:00'ASTimeSpan))--returns true

SELECTisnumberculture('1.1')--returns true in the en-us culture

SELECTisnumberculture('1,1')--returns false in the en-us culture

SELECTisnumberculture('1,1'COLLATEit-it_ci)--returns true

ISTIMESPAN

Returns true if the specified expression can be converted to a TimeSpan, otherwise false. If the input expression is a string, possible conversion to TimeSpan will be checked using the invariant culture.

SELECTistimespan(gettimeofday(now()))--returns true

SELECTistimespan(1)--returns true

SELECTistimespan('00:01:10.123')--returns true

SELECTistimespan('00:01:10,123')--returns false

ISTIMESPANCULTURE

Returns true if the specified expression can be converted to a TimeSpan, otherwise false. If the input expression is a string and the COLLATE keyword is not specified, the conversion is checked using the culture of the data model’s collation.

SELECTistimespanculture(gettimeofday(now()))--returns true

SELECTistimespanculture(1)--returns true

SELECTistimespanculture('00:01:10.123')--returns true in the en-us culture

SELECTistimespanculture('00:01:10,123')--returns false in the en-us culture

SELECTistimespanculture('00:01:10,123'COLLATEit-it_ci)--returns true