Skip to main content

%SYSTEM.SQL.Functions

abstract class %SYSTEM.SQL.Functions extends %SYSTEM.Help

Method Inventory

Methods

classmethod ABS(val As %Library.String = "") as %Library.Float
ABS returns the absolute value, which is always zero or a positive number

$SYSTEM.SQL.Functions.ABS(numeric-expression)

numeric-expression
A number whose absolute value is to be returned
ABS returns the same data type as numeric-expression. If numeric-expression is not a number (for example, the string 'abc') ABS returns 0. ABS returns NULL when passed a NULL value.

Also see the ABS reference page.

classmethod ALPHAUP(stringexp As %Library.String = "") as %Library.String
ALPHAUP returns the Alphaup collation of the passed in value.
$SYSTEM.SQL.Functions.ALPHAUP(stringexp)
stringexp Any string expression value.
ALPHAUP converts all alphabetic characters to upper case (i.e., the ALPHAUP format) and removes all punctuation except commas and question marks.
classmethod CEILING(val As %Library.String = "") as %Library.Float
CEILING is a scalar numeric function that returns the smallest integer greater than or equal to a given numeric expression.

$SYSTEM.SQL.Functions.CEILING(numeric-expression)

numeric-expression
A number whose ceiling is to be calculated

Also see the CEILING reference page.

classmethod COLLATE(stringexp As %Library.String = "", transSpec As %Library.String = "", maxlen As %Library.Integer) as %Library.String
COLLATE returns the COLLATE collation of the passed in value.

$SYSTEM.SQL.Functions.COLLATE(stringexp,transSpec,maxlen)

stringexp
Any string expression value.
transSpec
The transformation-spec is a comma-separated list of optional parameters to control the transformation.
maxlen
Optional. An integer, which specifies that the collated value will be truncated to the value of maxlen.
classmethod CONVERT(expr As %Library.String = "", convto As %Library.String = "", convfrom As %Library.String = "") as %Library.String
CONVERT is a SQL function that converts a given expression to a specified data type.

$SYSTEM.SQL.Functions.CONVERT(expr,convto,convfrom)

expr
The expression to be converted
convto
The data type to which expr is to be converted. Currently, only "SQL_TIMESTAMP", "SQL_POSIXTIME", "SQL_TIME", "SQL_DATE", "SQL_VARCHAR", "SQL_INTEGER", and "SQL_DOUBLE" are supported
convfrom
The data type of expr. Currently, only "SQL_TIMESTAMP", "SQL_POSIXTIME", "SQL_TIME", "SQL_DATE", "SQL_VARCHAR", "SQL_INTEGER", and "SQL_DOUBLE" are supported
CONVERT is a SQL function that converts expression expr from type convfrom to the specified data type convto.
The convfrom value is expected to be a Logical value for SQL_DATE and SQL_TIME. When converting from SQL_DATE, SQL_POSIXTIME, or SQL_TIME to SQL_TIMESTAMP, the input value is expected to be a logical %Library.Date, %Library.PosixTime, or %Library.Time value. When converting from SQL_VARCHAR to SQL_TIME, the input value is expected to be an ODBC %Library.Time formatted value. When converting from SQL_VARCHAR to SQL_DATE, the input value is expected to be an ODBC %Library.Date formatted value. When converting from SQL_DATE, SQL_POSIXTIME, or SQL_TIME to SQL_VARCHAR, the returned value will contain the ODBC format of the %Library.Date, %Library.PosixTime, or %Library.Time value.

Also see the CONVERT reference page.

classmethod DATE(exp As %Library.String = "") as %Library.Date
DATE is a date/time function that returns the date portion of the given date or timestamp expression.

$SYSTEM.SQL.Functions.DATE(exp)

exp
An expression that is a logical %Library.Date, %Library.TimeStamp, %Library.PosixTime, %Library.Integer, or %Library.String value.
If "" (null) is passed in, "" (null) is returned. If exp is not numeric, it is assumed the expression is in %Library.TimeStamp logical format. Note, that if a string value is passed in as exp, it is expected that the value is a logical %Library.TimeStamp format, and not $Horolog format. To convert $Horolog to DATE, use $SYSTEM.SQL.Functions.DATE(+$HOROLOG),

Also see the DATE reference page.

classmethod DATEADD(datepart As %Library.String = "", val As %Library.Numeric = "", dateexp As %Library.String = "") as %Library.TimeStamp
DATEADD is a general date/time function that returns a date calculated by adding a certain number of date parts (such as hours or days) to a specified timestamp

$SYSTEM.SQL.Functions.DATEADD(datepart,numeric-exp,dateexp)

datepart
The full name or abbreviation of a date or time part.
The following date parts are supported for DATEADD
year yy yyyy
quarter qq q
month mm m
week wk ww
dayofyear dy y
day dd d
hour hh
minute mi n
second ss s
millisecond ms
numeric-exp
A numeric expression of any number type. The value is truncated to an integer. The value indicates the number of dateparts that will be added to dateexp.
dateexp
The date/time expression that will be modified.
The dateexp parameter can be in any of the following formats, and may include or omit fractional seconds:
  • A %Library.Date logical value (+$H)
  • A %Library.PosixTime logical value
  • A %Library.TimeStamp logical value (YYYY-MM-DD HH:MM:SS)
  • A %Library.String (or compatible) value
  • The %Library.String (or compatible) value can be in any of the following formats:
    %Library.PosixTime logical value 99999,99999 ($H format)
    Sybase/SQL-Server-date Sybase/SQL-Server-time
    Sybase/SQL-Server-time Sybase/SQL-Server-date
    Sybase/SQL-Server-date (default time is 00:00:00)
    Sybase/SQL-Server-time (default date is 01/01/1900)
    Sybase/SQL-Server-date is one of these five formats:
    mmdelimiterdddelimiter[yy]yy
    dd Mmm[mm][,][yy]yy
    dd [yy]yy Mmm[mm]
    yyyy Mmm[mm] dd
    yyyy [dd] Mmm[mm]
    where delimiter is a slash (/), hyphen (-), or period (.).
    Sybase/SQL-Server-time represents one of these three formats:
    HH:MM[:SS:SSS][{AM|PM}]
    HH:MM[:SS.S]
    HH['']{AM|PM}
If the year is given as two digits, a sliding window is checked to interpret the date. The system default for the sliding window can be set via the %DATE utility, which is documented only in the "Legacy Documentation" chapter in Using InterSystems Documentation.

Also see the DATEADD reference page.

classmethod DATEDIFF(datepart As %Library.String = "", startdate As %Library.String = "", enddate As %Library.String = "") as %Library.Integer
DATEDIFF is a general date/time function that returns the interval between two dates

$SYSTEM.SQL.Functions.DATEDIFF(datepart,startdate,enddate)

datepart
The date part in which the interval is to be measured.
The following date parts are supported for DATEDIFF
year yy yyyy
month mm m
week wk ww
day dd d
hour hh
minute mi n
second ss s
millisecond ms
startdate
The starting date for the interval.
enddate
The ending date for the interval.
Startdate is subtracted from enddate to determine how many datepart intervals are between the two dates.
The startdate and enddate parameters can be in any of the following formats:
  • A %Library.Date logical value (+$H)
  • A %Library.PosixTime logical value
  • A %Library.TimeStamp logical value (YYYY-MM-DD HH:MM:SS)
  • A %Library.String (or compatible) value
  • The %Library.String (or compatible) value can be in any of the following formats, and may include or omit fractional seconds:

  • 99999,99999 ($HOROLOG format)
    Sybase/SQL-Server-date Sybase/SQL-Server-time
    Sybase/SQL-Server-time Sybase/SQL-Server-date
    Sybase/SQL-Server-date (default time is 00:00:00)
    Sybase/SQL-Server-time (default date is 01/01/1900)
  • Sybase/SQL-Server-date is one of these five formats:

  • mmdelimiterdddelimiter[yy]yy
    dd Mmm[mm][,][yy]yy
    dd [yy]yy Mmm[mm]
    yyyy Mmm[mm] dd
    yyyy [dd] Mmm[mm]
    where delimiter is a slash (/), hyphen (-), or period (.).
  • Sybase/SQL-Server-time represents one of these three formats:

  • HH:MM[:SS:SSS][{AM|PM}]
    HH:MM[:SS.S]
    HH['']{AM|PM}
If the year is given as two digits, a sliding window is checked to interpret the date. The system default for the sliding window can be set via the %DATE utility, which is documented only in the "Legacy Documentation" chapter in Using InterSystems Documentation.

Also see the DATEDIFF reference page.

classmethod DATENAME(datepart As %Library.String = "", dateexp As %Library.String = "") as %Library.String
DATENAME is a general date/time function that returns a CHARACTER STRING containing the name of the specified date part of a date/time value.

$SYSTEM.SQL.Functions.DATENAME(datepart,dateexp)

datepart
The type of date part that the returned value will represent.
The following date parts are supported for DATENAME
year yy yyyy
quarter qq q
month mm m
week wk ww
weekday dw
dayofyear dy y
day dd d
hour hh
minute mi n
second ss s
millisecond ms
dateexp
A date/time expression from which the date part is to be returned.
The dateexp parameter can be in any of the following formats:
  • A %Library.Date logical value (+$H)
  • A %Library.PosixTime logical value
  • A %Library.TimeStamp logical value (YYYY-MM-DD HH:MM:SS)
  • A %Library.String (or compatible) value
  • The %Library.String (or compatible) value can be in any of the following formats, and may include or omit fractional seconds:

  • 99999,99999 ($HOROLOG format)
    Sybase/SQL-Server-date Sybase/SQL-Server-time
    Sybase/SQL-Server-time Sybase/SQL-Server-date
    Sybase/SQL-Server-date (default time is 00:00:00)
    Sybase/SQL-Server-time (default date is 01/01/1900)
  • Sybase/SQL-Server-date is one of these five formats:

  • mmdelimiterdddelimiter[yy]yy
    dd Mmm[mm][,][yy]yy
    dd [yy]yy Mmm[mm]
    yyyy Mmm[mm] dd
    yyyy [dd] Mmm[mm]
    where delimiter is a slash (/), hyphen (-), or period (.).
  • Sybase/SQL-Server-time represents one of these three formats:

  • HH:MM[:SS:SSS][{AM|PM}]
    HH:MM[:SS.S]
    HH['']{AM|PM}
If the year is given as two digits, a sliding window is checked to interpret the date. The system default for the sliding window can be set via the %DATE utility, which is documented only in the "Legacy Documentation" chapter in Using InterSystems Documentation.

Also see the DATENAME reference page.

classmethod DATEPART(datepart As %Library.String = "", dateexp As %Library.String = "") as %Library.Integer
DATEPART is a general date/time function that returns an INTEGER representing the specified date/time part of the specified date/time expression.

$SYSTEM.SQL.Functions.DATEPART(datepart,dateexp)

datepart
The type of date part that the returned value will represent.
The following date parts are supported for DATEPART
year yy yyyy
quarter qq q
month mm m
week wk ww
weekday dw
dayofyear dy y
day dd d
hour hh
minute mi n
second ss s
millisecond ms
sqltimestamp sts
The sqltimestamp (abbreviated sts) datepart value is for use only with DATEPART. Do not attempt to use this parameter value in other contexts
dateexp
A date/time expression from which the date part is to be returned.
dateexp must contain a value of type datepart.
The dateexp parameter can be in any of the following formats:
  • A %Library.Date logical value (+$H)
  • A %Library.PosixTime logical value
  • A %Library.TimeStamp logical value (YYYY-MM-DD HH:MM:SS)
  • A %Library.String (or compatible) value
  • The %Library.String (or compatible) value can be in any of the following formats, and may include or omit fractional seconds:

  • 99999,99999 ($HOROLOG format)
    Sybase/SQL-Server-date Sybase/SQL-Server-time
    Sybase/SQL-Server-time Sybase/SQL-Server-date
    Sybase/SQL-Server-date (default time is 00:00:00)
    Sybase/SQL-Server-time (default date is 01/01/1900)
  • Sybase/SQL-Server-date is one of these five formats:

  • mmdelimiterdddelimiter[yy]yy
    dd Mmm[mm][,][yy]yy
    dd [yy]yy Mmm[mm]
    yyyy Mmm[mm] dd
    yyyy [dd] Mmm[mm]
    where delimiter is a slash (/), hyphen (-), or period (.).
  • Sybase/SQL-Server-time represents one of these three formats:

  • HH:MM[:SS:SSS][{AM|PM}]
    HH:MM[:SS.S]
    HH['']{AM|PM}
If the year is given as two digits, a sliding window is checked to interpret the date. The system default for the sliding window can be set via the %DATE utility, which is documented only in the "Legacy Documentation" chapter in Using InterSystems Documentation.

Also see the DATEPART reference page.

classmethod DATETRUNC(datepart As %Library.String = "", tdate As %Library.String = "") as %Library.Integer
DATETRUNC is a general date/time function that truncates a date to

$SYSTEM.SQL.Functions.DATETRUNC(datepart,dateexp)

datepart
The type of date part that the returned value will represent.
The following date parts are supported for DATEDIFF
YEAR YYYY YY
QUARTER QQ Q
MONTH MM M
WEEK WK WW
WEEKDAY DW W
DAY DD D
DAYOFYEAR DY Y
HOUR HH H
MINUTE MI N
SECOND SS S
MILLISECOND MS
MICROSECOND MCS
NANOSECOND NS
dateexp
A date/time expression to be truncated to a granularity specified by datepart
The dateexp parameter can be in any of the following formats:
  • A %Library.Date logical value (+$H)
  • A %Library.PosixTime logical value
  • A %Library.TimeStamp logical value (YYYY-MM-DD HH:MM:SS)
  • A %Library.String (or compatible) value
  • The %Library.String (or compatible) value can be in any of the following formats, and may include or omit fractional seconds:

  • 99999,99999 ($HOROLOG format)
    Sybase/SQL-Server-date Sybase/SQL-Server-time
    Sybase/SQL-Server-time Sybase/SQL-Server-date
    Sybase/SQL-Server-date (default time is 00:00:00)
    Sybase/SQL-Server-time (default date is 01/01/1900)
  • Sybase/SQL-Server-date is one of these five formats:

  • mmdelimiterdddelimiter[yy]yy
    dd Mmm[mm][,][yy]yy
    dd [yy]yy Mmm[mm]
    yyyy Mmm[mm] dd
    yyyy [dd] Mmm[mm]
    where delimiter is a slash (/), hyphen (-), or period (.).
  • Sybase/SQL-Server-time represents one of these three formats:

  • HH:MM[:SS:SSS][{AM|PM}]
    HH:MM[:SS.S]
    HH['']{AM|PM}
If the year is given as two digits, a sliding window is checked to interpret the date. The system default for the sliding window can be set via the %DATE utility, which is documented only in the "Legacy Documentation" chapter in Using InterSystems Documentation.

classmethod DAYNAME(dateexp As %Library.String = "") as %Library.String
DAYNAME is a date/time function that returns a character string containing the name of the day in a given date or datetime expression.

$SYSTEM.SQL.Functions.DAYNAME(dateexp)

dateexp
An expression that is a logical %Library.Date, %Library.TimeStamp, or %Library.PosixTime value.
If the year is given as two digits, a sliding window is checked to interpret the date. The system default for the sliding window can be set via the %DATE utility, which is documented only in the "Legacy Documentation" chapter in Using InterSystems Documentation.

Also see the DAYNAME reference page.

classmethod DAYOFMONTH(dateexp As %Library.String = "") as %Library.Integer
DAYOFMONTH is a date/time function that returns an integer from 1 to 31 that corresponds to the day of the month in a given date expression.

$SYSTEM.SQL.Functions.DAYOFMONTH(dateexp)

dateexp
An expression that is a logical %Library.Date, %Library.TimeStamp, or %Library.PosixTime.

Also see the DAYOFMONTH reference page.

classmethod DAYOFWEEK(dateexp As %Library.String = "") as %Library.Integer
DAYOFWEEK is a date/time function that returns an integer from 1 to 7 that corresponds to the day of the week in a given date expression.

$SYSTEM.SQL.Functions.DAYOFWEEK(dateexp)

dateexp
An expression that is a logical %Library.Date value, %Library.TimeStamp, %Library.PosixTime, or an $Horolog value.
The returned values represent these days: 1 - Sunday, 2 - Monday, 3 - Tuesday, 4 - Wednesday, 5 - Thursday, 6 - Friday, 7 - Saturday
The default is that Sunday is the first day of the week.
This default can be overridden by specifying SET ^%SYS("sql","sys","day of week")=n, where n values are 1=Monday through 7=Sunday.
Thus, to reset the default specify SET ^%SYS("sql","sys","day of week")=7.
The day of week setting can also be defined on a per-namespace basis by adding an additional namespace subscript, for example:
SET ^%SYS("sql","sys","day of week","USER")=n, where n values are 1=Monday through 7=Sunday.
InterSystems IRIS also supports the ISO 8601 standard for determining the day of the week, week of the year, and other date settings. This standard is principally used in European countries. The ISO 8601 standard begins counting the days of the week with Monday. To activate ISO 8601, SET ^%SYS("sql","sys","week ISO8601")=1; to deactivate, set it to 0. If week ISO8601 is activated and "day of the week" is undefined or set to the default (7=Sunday), the ISO 8601 standard overrides the default. If "day of the week" is set to any other value, it overrides week ISO8601 for DAYOFWEEK.

Also see the DAYOFWEEK reference page.

classmethod DAYOFYEAR(dateexp As %Library.String = "") as %Library.Integer
DAYOFYEAR is a date/time function that returns an integer from 1 to 366 that corresponds to the day of the year in a given date expression.

$SYSTEM.SQL.Functions.DAYOFYEAR(dateexp)

dateexp
An expression that is a logical %Library.Date value, %Library.TimeStamp, or %Library.PosixTime value.

Also see the DAYOFYEAR reference page.

classmethod FLOOR(val As %Library.Numeric = "") as %Library.Integer
FLOOR is a numeric function that returns the largest integer less than or equal to a given numeric expression

$SYSTEM.SQL.Functions.FLOOR(dateexp)

numeric-exp
A number whose floor is to be calculated.

Also see the FLOOR reference page.

classmethod HOUR(timeexp As %Library.String = "") as %Library.Integer
HOUR is a date/time function that returns an integer from 0 to 23 that corresponds to the hour component in a given date-time expression.

$SYSTEM.SQL.Functions.HOUR(timeexp)

timeexp
An expression that is a logical %Library.Time, %Library.TimeStamp, or %Library.PosixTime value.

Also see the HOUR reference page.

classmethod INSTR(string As %Library.String = "", substring As %Library.String = "", position As %Library.Integer = 1, occurrence As %Library.Integer = 1) as %Library.Integer
INSTR is a function that searches string for substring and returns an integer indicating the position of the character in string that is the first character of this occurrence.

$SYSTEM.SQL.Functions.INSTR(string,substring,position,occurrence)

string
The string to search in.
substring
The string to search for in string.
position
A nonzero integer indicating the character of string where InterSystems IRIS begins the search. If position is negative, then InterSystems IRIS counts backward from the end of string and then searches backward from the resulting position.
occurrence
An integer indicating which occurrence of substring InterSystems IRIS should search for. If occurrence is greater than 1, then InterSystems IRIS searches for the second occurrence beginning with the second character in the first occurrence of string, and so forth. An occurrence of less than 1 is treated the same as 1.

Also see the INSTR reference page.

classmethod LASTDAY(dateexp As %Library.String = "") as %Library.Date
LASTDAY is a scalar date/time function that returns the %Library.Date value of the last day of the month for a given date expression.

$SYSTEM.SQL.Functions.LASTDAY(dateexp)

dateexp
A %Library.Date, %Library.TimeStamp, or %Library.PosixTime logical value.
LASTDAY returns the date of the last day of the specified month as a %Library.Date value. Leap years differences are calculated.

LASTDAY returns 0 when an invalid date is specified: the day or month as zero; the month greater than 12; or the day larger than the number of days in that month on that year.

classmethod MINUTE(timeexp As %Library.String = "") as %Library.Integer
MINUTE is a date/time function that returns an integer from 0 to 59 that corresponds to the minute component in a given date-time expression.

$SYSTEM.SQL.Functions.MINUTE(timeexp)

timeexp
An expression that is a logical %Library.Time, %Library.TimeStamp, or %Library.PosixTime value.

Also see the MINUTE reference page.

classmethod MONTH(dateexp As %Library.String = "") as %Library.Integer
MONTH is a date/time function that returns an integer from 1 to 12 that corresponds to the month in a given date expression.

$SYSTEM.SQL.Functions.MONTH(dateexp)

dateexp
An expression that is a logical %Library.Date, %Library.TimeStamp, or %Library.PosixTime value.

Also see the MONTH reference page.

classmethod MONTHNAME(dateexp As %Library.String = "") as %Library.String
MONTHNAME is a date/time function that returns a character string containing the name of the month in a given date expression.

$SYSTEM.SQL.Functions.MONTHNAME(dateexp)

dateexp
An expression that is a logical %Library.Date, %Library.TimeStamp, or %Library.PosixTime value.

Also see the MONTHNAME reference page.

classmethod MVR(stringexp As %Library.String = "") as %Library.String
MVR returns the MVR collation of the passed in value.

$SYSTEM.SQL.Functions.MVR(stringexp)

stringexp
Any string expression value.
MVR performs collation translations needed for MultiValue
classmethod QUARTER(dateexp As %Library.String = "") as %Library.Integer
QUARTER is a date/time function that returns an integer from 1 to 4 that corresponds to the quarter of the year in a given date expression.

$SYSTEM.SQL.Functions.QUARTER(dateexp)

dateexp
An expression that is a logical %Library.Date, %Library.TimeStamp, or %Library.PosixTime value.

Also see the QUARTER reference page.

classmethod SECOND(timeexp As %Library.String = "") as %Library.Integer
SECOND is a date/time function that returns an integer from 0 to 59 that corresponds to the second component in a given date-time expression.

$SYSTEM.SQL.Functions.SECOND(timeexp)

timeexp
An expression that is a logical %Library.Time, %Library.TimeStamp, or %Library.PosixTime value.

Also see the SECOND reference page.

classmethod SQLCODE(SQLCODE As %Library.Integer) as %Library.String [ SQLProc = Functions_SQLCODE ]
Projected as the stored procedure: Functions_SQLCODE
This entry point can be used to return the description of an SQLCODE value.

Parameters:
SQLCODE
SQLCODE value.

Examples:

  • Write $SYSTEM.SQL.Functions.SQLCODE(-105)
  • Write $SYSTEM.SQL.Functions.SQLCODE(100)
This method can also be called as a Stored Procedure named %SYSTEM_SQL.Functions_SQLCODE(SQLCODE)
classmethod SQLSTRING(stringexp As %Library.String = "", maxlen As %Library.Integer) as %Library.String
SQLSTRING returns the SqlString collation of the passed in value.

$SYSTEM.SQL.Functions.SQLSTRING(stringexp,maxlen)

stringexp
Any string expression value.
maxlen
Optional. An integer, which specifies that the collated value will be truncated to the value of maxlen.
SQLSTRING converts stringexp to a format that is sorted as a (case-sensitive) string. SQLSTRING strips trailing white space (spaces, tabs, and so on) from the string, then adds one leading blank space to the beginning of the string. It collates any value containing only white space (spaces, tabs, and so on) as the SQL empty string. SQL Empty string is represented as $Char(0) in ObjectScript, which collated as SqlString is " "_$Char(0).
classmethod SQLUPPER(stringexp As %Library.String = "", maxlen As %Library.Integer) as %Library.String
SQLUPPER returns the SqlUpper collation of the passed in value.

$SYSTEM.SQL.Functions.SQLUPPER(stringexp,maxlen)

stringexp
Any string expression value.
maxlen
Optional. An integer, which specifies that the collated value will be truncated to the value of maxlen.
SQLUPPER converts stringexp to a format that is sorted as a (case-insensitive) upper-case string. SQLUPPER converts all alphabetic characters to uppercase, strips trailing white space (spaces, tabs, and so on) from the string, then adds one leading blank space to the beginning of the string. It collates any value containing only white space (spaces, tabs, and so on) as the SQL empty string. SQL Empty string is represented as $Char(0) in ObjectScript, which collated as SqlUpper is " "_$Char(0).
classmethod STRING(stringexp As %Library.String = "", maxlen As %Library.Integer) as %Library.String
STRING returns the String collation of the passed in value.

$SYSTEM.SQL.Functions.STRING(stringexp,maxlen)

stringexp
Any string expression value.
maxlen
Optional. An integer, which specifies that the collated value will be truncated to the value of maxlen.
STRING converts stringexp to a STRING collation format.
STRING converts all alphabetic characters are uppercased; all punctuation characters are removed, except the comma; a single space is added to the beginning of the expression. It collates any value containing only white space (spaces, tabs, and so on) as the SQL empty string. SQL Empty string is represented as $Char(0) in ObjectScript, which collated as String is " "_$Char(0).
classmethod TOCHAR(expr As %Library.String = "", format As %Library.String = "") as %Library.String
TOCHAR is a general SQL string function that converts a given date, timestamp, or number value to a string.

$SYSTEM.SQL.Functions.TOCHAR(expr,format)

expr
A Logical %Library.Date, logical %Library.TimeStamp, logical %Library.Time value, $Horolog, or number expression to be converted.
format
A date or number format specifying the format for the expr conversion. If format contains the characters "Y", "MM", "RR", "DD", "J", "HH", "MI", "SS", "MON", "MONTH", "AM", "PM", or "D", expr is assumed to be a %Library.Date, %Library.TimeStamp, or $Horolog value.
If format contains the characters "HH", "MI", "SS", "AM", or "PM" and it does not contain a date format, expr is assumed to be a %Library.Time value.
Otherwise, it is a numeric value.
The first use of TOCHAR is to convert a date, time, or datetime expression to a string.
The second use of TOCHAR is to convert a number to a string.

Also see the TO_CHAR reference page.

classmethod TODATE(dateexp As %Library.String = "", format As %Library.String = "") as %Library.String
TODATE is a general SQL string function that converts a given string expression to a value of DATE data type.

$SYSTEM.SQL.Functions.TODATE(expr,format)

expr
The expression to be converted. The expression can be a string date expression, the name of a column, or a value where the underlying format is of type CHAR or VARCHAR. It can also be an integer between 1 and 2980013.
format
A date format specifying the format for expr. If 'J' is specified, expr must be an integer. If format is omitted, 'DD MON YYYY' is the default value.
The use of TODATE is to specify the input format of a string value containing a date to be converted to a Logical %Library.Date value. The format of expr is specified in the format parameter. format will be used as a key to translate expr into a valid %Library.Date logical value.

Also see the TODATE reference page.

classmethod TOPOSIXTIME(stringexp As %Library.String = "", format As %Library.String = "") as %Library.String
TOPOSIXTIME is a general SQL string function that converts a given string expression to a logical value of a %Library.PosixTime data type.

$SYSTEM.SQL.Functions.TOPOSIXTIME(stringexp,format)

stringexp
The expression to be converted. The expression can be a string datetime expression, the name of a column, or a value where the underlying format is of type CHAR or VARCHAR.
format
A datetime format code specifying the format for stringexp. If format is omitted, 'DD MON YYYY HH:MM:SS' is the default value.
The use of TOPOSIXTIME is to specify the input format of a string value containing a datetime to be converted to a Logical %Library.PosixTime value. The format of stringexp is specified in the format parameter. format will be used as a key to translate stringexp into a valid %Library.PosixTime logical value. If an error occurs during the execution of TOPOSIXTIME, the function will return a value of 0 and an error message will be defined in the %msg variable.

Also see the TO_POSIXTIME reference page.

classmethod TOTIMESTAMP(stringexp As %Library.String = "", format As %Library.String = "") as %Library.String
TOTIMESTAMP is a general SQL string function that converts a given string expression to a value of TIMESTAMP data type.

$SYSTEM.SQL.Functions.TOTIMESTAMP(stringexp,format)

stringexp
The expression to be converted. The expression can be a string datetime expression, the name of a column, or a value where the underlying format is of type CHAR or VARCHAR.
format
A datetime format code specifying the format for stringexp. If format is omitted, 'DD MON YYYY HH:MI:SS' is the default value.
The use of TOTIMESTAMP is to specify the input format of a string value containing a datetime to be converted to a Logical %Library.TimeStamp value. The format of stringexp is specified in the format parameter. format will be used as a key to translate stringexp into a valid %Library.TimeStamp logical value. If an error occurs during the execution of TOTIMESTAMP, the function will return a value of 0 and an error message will be defined in the %msg variable.

Also see the TO_TIMESTAMP reference page.

classmethod TRUNCATE(stringexp As %Library.String = "", maxlen As %Library.Integer = 32768) as %Library.String
TRUNCATE returns the Truncate collation of the passed in value.

$SYSTEM.SQL.Functions.TRUNCATE(stringexp,maxlen)

stringexp
Any string expression value.
maxlen
Optional. An integer, which specifies that the collated value will be truncated to the length of maxlen. If maxlen is not specified, TRUNCATE behaves the same as EXACT.
TRUNCATE leaves stringexp in the exact format it receives and is sorted as a (case-sensitive) string. TRUNCATE simply returns the first maxlen characters of the expression.

Also see the TRUNCATE reference page.

classmethod UPPER(stringexp As %Library.String = "") as %Library.String
UPPER returns the Upper collation of the passed in value.

$SYSTEM.SQL.Functions.UPPER(stringexp)

stringexp
Any string expression value.

UPPER converts all alphabetic characters to upper case (i.e., the UPPER format). Note that punctuation is not changed.

Also see the UPPER reference page.

classmethod VECTORCOSINE(vec1 As %Library.Vector = "", vec2 As %Library.Vector = "") as %Library.Double
VECTORCOSINE returns the cosine of the angle between two vectors, i.e., the dot product of the vectors divided by the product of their lengths.

$SYSTEM.SQL.Functions.VECTORCOSINE(vec1,vec2)

vec1,vec2
Any vector.

VECTORCOSINE returns the cosine distance of vectors vec1 and vec2, i.e.,

the sum of the products of each pair of elements in the same position of the two input vectors, divided by a product of their lengths.

Also see the VECTORCOSINE reference page.

classmethod VECTORDOTPRODUCT(vec1 As %Library.Vector = "", vec2 As %Library.Vector = "") as %Library.Double
VECTORDOTPRODUCT returns the dot product of two vectors.

$SYSTEM.SQL.Functions.VECTORDOTPRODUCT(vec1,vec2)

vec1,vec2
Any vector.

VECTORDOTPRODUCT returns the dot product of vectors vec1 and vec2, i.e., the sum of the products of each pair of elements in the same position of the two input vectors.

Also see the VECTORDOTPRODUCT reference page.

classmethod WEEK(dateexp As %Library.String = "") as %Library.Integer
WEEK is a date/time function that returns an integer from 1 to 53 that corresponds to the week of the year in a given date expression.

$SYSTEM.SQL.Functions.WEEK(dateexp)

dateexp
An expression that is a logical %Library.Date, %Library.TimeStamp, or %Library.PosixTime value.

Also see the WEEK reference page.

classmethod YEAR(dateexp As %Library.String = "") as %Library.Integer
YEAR is a date/time function that returns an integer in the range 1840-9999 that indicates the year in a given date expression.

$SYSTEM.SQL.Functions.YEAR(dateexp)

dateexp
An expression that is a logical %Library.Date, %Library.TimeStamp, or %Library.PosixTime value.

Also see the YEAR reference page.

Inherited Members

Inherited Methods

FeedbackOpens in a new tab