Data Type Casting Functions
- _add_days
Returns the datetime resulting from adding "integer_expression" days to "timestamp_expression". - _add_months
Returns the datetime resulting from adding "integer_expression" months to "timestamp_expression". - _add_years
Returns the datetime resulting from adding "integer_expression" years to "timestamp_expression". - _age
Returns a number by subtracting "timestamp_expression" from today's date. - _day_of_week
Returns the day of the week (between 1 and 7) for "timestamp_expression" where "integer_expression" indicates which day of that week is day 1. To determine "integer_expression", choose the day of the week and count from Monday; for example, if you choose Wednesday, "integer_expression" would be 3 because Wednesday is the third day from Monday. - _day_of_year
Returns the ordinal for the day of the year in "timestamp_ expression" (1 to 366). Also known as Julian day. - _days_between
Returns a positive or negative number representing the number of days between "timestamp_expression1" and "timestamp_expression2". If "timestamp_expression1" < "timestamp_expression2", the result will be a negative number. - _days_to_end_of_month
Returns a number representing the number of days remaining in the month represented by "timestamp_expression". - _first_of_month
Returns a datetime that is the first day of the month represented by "timestamp_expression". - _last_of_month
Returns a datetime that is the last day of the month represented by "timestamp_expression". - _make_timestamp
Returns a timestamp constructed from "integer_expression1" (the year), "integer_expression2" (the month), and "integer_expression3" (the day). The time portion defaults to 00:00:00.000 . - _months_between
Returns a positive or negative number representing the number of months between "timestamp_expression1" and "timestamp_expression2". If "timestamp_expression1" < "timestamp_expression2", the result will be a negative number. - _week_of_year
Returns the week number (1-53) of the year for "timestamp_expression". According to the ISO 8601, week 1 of the year is the first week to contain a Thursday, which is equivalent to the first week containing January 4th. A week starts on a Monday (day 1) and ends on a Sunday (day 7). - _years_between
Returns a positive or negative integer representing the number of years between "timestamp_expression1" and "timestamp_expression2". If "timestamp_expression1" < "timestamp_expression2", a negative value is returned. - _ymdint_between
Returns a number representing the difference between "timestamp_expression1" and "timestamp_expression2". This value has the form YYMMDD, where YY represents the number of years, MM represents the number of months, and DD represents the number of days. - abs
Returns the absolute value of "numeric_expression". If "numeric_expression" is negative, a positive value is returned. - ceiling
Returns the smallest integer that is greater than or equal to "numeric_expression". - character_length
Returns the number of characters in "string_expression". - date2string
Returns a date as a string in YYYY-MM-DD format. - date2timestamp
Converts "date_expression" to a timestamp. The time part of the timestamp will equal zero. - date2timestampTZ
Converts "date_expression" to a timestamp with a time zone. The time and time zone parts of the timestamp will equal zero. - DTinterval2string
Returns a date time interval as a string in DDDD HH:MM:SS.FFFFFFF or -DDDD HH:MM:SS.FFF format. - DTinterval2stringAsTime
Returns a date time interval as a string in HHHH:MM:SS.FFFFFFF or HH:MM:SS.FFF format. Days are converted to hours. - exp
Returns the constant 'e' raised to the power of "numeric_expression". The constant 'e' is the base of the natural logarithm. - extract
Returns an integer representing the value of "date_part_expression" in "datetime_expression". "Date_part_expression" could be the year, month, day, hour, minute, or second. - floor
Returns the largest integer that is less than or equal to "numeric_expression". - int2DTinterval
Converts an integer to a date time interval. "String_expression" specifies what "integer_expression" represents: "ns" = nanoseconds, "s" = seconds (default), "m" = minutes, "h" = hours, "d" = days. - int2YMinterval
Converts "integer_expression" to a year month interval. "String_expression" specifies what "integer_expression" represents: "y" = years, "m" = months (default). - ln
Returns the natural logarithm of "numeric_expression". - lower
Returns "string_expression" with all uppercase characters converted to lowercase. This function appears in the Bursted Sales Performance Report sample report in the GO Data Warehouse (query) package. - mapNumberToLetter
Adds "integer_expression" to "string_expression". - mod
Returns an integer value representing the remainder (modulo) of "integer_expression1" / "integer_expression2". - nullif
Returns null if "string_expression1" equals "string_expression2" (case-insensitive), otherwise returns "string_expression1". - number2string
Converts "numeric_expression" to a string, using the %g format specifier (C/C++ syntax). - octet_length
Returns the number of bytes in "string_expression". - position
Returns the integer value representing the starting position of "string_expression1" in "string_expression2". Returns 0 if "string_expression1" is not found. - power
Returns "numeric_expression1" raised to the power of "numeric_expression2". - round
Returns "numeric_expression" rounded to the nearest value with "integer_expression" significant digits to the right of the decimal point. If "integer_expression" is negative, "numeric_expression" is rounded to the nearest absolute value with "integer_expression" significant digits to the left of the decimal point. Rounding takes place before data formatting is applied. - sqrt
Returns the square root of "numeric_expression". "Numeric_expression" must not be a negative value. - status
Returns the status of "expression". Possible values are: 0 - OK, 1 - null, 2 - not available, 4 - divide by zero, 8 - overflow, 16 - security, 32 - error, 64 - new, 128 - sample, 256 - pending. - string2date
Returns "string_expression" as a date in YYYY-MM-DD format. - string2double
Returns a floating point number. "String_expression" has the following form: "[whitespace] [sign] [digits] [digits] [ {d|D|e|E }[sign]digits]" - string2DTinterval
Returns "string_expression" as a date time interval in [-]DD HH:MM[:SS[.FFF]] format. - string2int32
Returns an integer. "String_expression" has the following form: "[whitespace] [{+|-}] [digits]" - string2int64
Returns a long integer. "String_expression" has the following form: "[whitespace] [{+|-}] [digits]" - string2time
Returns "string_expression" as a time in HH:MM:SS.FFFFFFF format. - string2timestamp
Returns "string_expression" as a timestamp in YYYY-MM-DD [T|t|[white space]+] HH:MM:SS.FFFFFFF format. - string2timestampTZ
Returns "string_expression" in YYYY-MM-DD HH:MM:SS.FFFFFFF +HHMM or YYYY-MM-DD [T|t] HH:MM:SS.FFF -HHMM format. - string2YMinterval
Returns "string_expression" as a Year Month Interval in [-]YY MM format. - substring
Returns the substring of "string_expression" that starts at position "integer_expression1" for "integer_expression2" characters or to the end of "string_expression" if "integer_expression2" is -1. The first character in "string_expression" is at position 1. - time2string
Returns a time as a string in HH:MM:SS.FFF format. - timestamp2date
Converts "timestamp_expression" to a date. The time part of the timestamp will be ignored. - timestamp2string
Returns a timestamp as a string in YYYY-MM-DD HH:MM:SS.FFFFFFF format. - timestamp2timestampTZ
Converts "timestamp_expression" to a timestamp with a time zone. The displacement part of the timestamp with the time zone will be zero. - timestampTZ2date
Converts "timestamp_time_zone_expression" to a date. The time and time zone parts of the timestamp will be ignored. - timestampTZ2string
Returns a timestamp with the time zone as a string in YYYY-MM-DD HH:MM:SS.FFFFFFF +HHMM or YYYY-MM-DD HH:MM:SS.FFF -HHMM format. - timestampTZ2timestamp
Converts "timestamp_time_zone_expression" to a timestamp. The displacement part of the timestamp with the time zone will be ignored. - timeTZ2string
Returns a time with the time zone as a string in HH:MM:SS.FFF +HHMM or HH:MM:SS.FFFFFFF -HHMM format. For example, -05:30 means a timezone of GMT minus 5 hours and 30 minutes - trim
Returns "string_expression" trimmed of any leading and trailing blanks or trimmed of the character specified by "match_character_expression". "Trim_what_expression" may be "leading", "trailing", or "both" (default). "Match_character_expression" can be an empty string to trim blanks or can specify a character to be trimmed. - upper
Returns "string_expression" with all lowercase characters converted to uppercase. - YMinterval2string
Returns "year_month_interval_expression" as a string in (YY MM) or -(YY MM) format.
Parent topic: Using the expression editor