Skip to main content

%SYSTEM.SQL.Functions

Class %SYSTEM.SQL.Functions Extends %SYSTEM.Help [ Abstract, Not ProcedureBlock, System = 4 ]

Methods​

ABS​

ClassMethod ABS(val As %Library.String = "") As %Library.Float [ ProcedureBlock = 1 ]

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.

ALPHAUP​

ClassMethod ALPHAUP(stringexp As %Library.String = "") As %Library.String [ ProcedureBlock = 1 ]

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.

FLOOR​

ClassMethod FLOOR(val As %Library.Numeric = "") As %Library.Integer [ ProcedureBlock = 1 ]

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.

CEILING​

ClassMethod CEILING(val As %Library.String = "") As %Library.Float [ ProcedureBlock = 1 ]

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.

CONVERT​

ClassMethod CONVERT(expr As %Library.String = "", convto As %Library.String = "", convfrom As %Library.String = "") As %Library.String [ ProcedureBlock = 1 ]

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.

DATE​

ClassMethod DATE(exp As %Library.String = "") As %Library.Date [ ProcedureBlock = 1 ]

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.

DATEADD​

ClassMethod DATEADD(datepart As %Library.String = "", val As %Library.Numeric = "", dateexp As %Library.String = "") As %Library.TimeStamp [ ProcedureBlock = 1, PublicList = SQLCODE ]

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:
    mm_delimiter_dd_delimiter_[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.

DATEDIFF​

ClassMethod DATEDIFF(datepart As %Library.String = "", startdate As %Library.String = "", enddate As %Library.String = "") As %Library.Integer [ ProcedureBlock = 1, PublicList = SQLCODE ]

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:

mm_delimiter_dd_delimiter_[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.

DATETRUNC​

ClassMethod DATETRUNC(datepart As %Library.String = "", tdate As %Library.String = "") As %Library.Integer [ ProcedureBlock = 1, PublicList = SQLCODE ]

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:

mm_delimiter_dd_delimiter_[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.

DATENAME​

ClassMethod DATENAME(datepart As %Library.String = "", dateexp As %Library.String = "") As %Library.String [ ProcedureBlock = 1, PublicList = SQLCODE ]

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:

mm_delimiter_dd_delimiter_[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.

DATEPART​

ClassMethod DATEPART(datepart As %Library.String = "", dateexp As %Library.String = "") As %Library.Integer [ ProcedureBlock = 1, PublicList = SQLCODE ]

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:

mm_delimiter_dd_delimiter_[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.

DAYNAME​

ClassMethod DAYNAME(dateexp As %Library.String = "") As %Library.String [ ProcedureBlock = 1 ]

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.

DAYOFMONTH​

ClassMethod DAYOFMONTH(dateexp As %Library.String = "") As %Library.Integer [ ProcedureBlock = 1 ]

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.

DAYOFWEEK​

ClassMethod DAYOFWEEK(dateexp As %Library.String = "") As %Library.Integer [ ProcedureBlock = 1, PublicList = SQLCODE ]

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.

DAYOFYEAR​

ClassMethod DAYOFYEAR(dateexp As %Library.String = "") As %Library.Integer [ ProcedureBlock = 1 ]

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.

HOUR​

ClassMethod HOUR(timeexp As %Library.String = "") As %Library.Integer [ ProcedureBlock = 1 ]

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.

INSTR​

ClassMethod INSTR(string As %Library.String = "", substring As %Library.String = "", position As %Library.Integer = 1, occurrence As %Library.Integer = 1) As %Library.Integer [ ProcedureBlock = 1 ]

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.

LASTDAY​

ClassMethod LASTDAY(dateexp As %Library.String = "") As %Library.Date [ ProcedureBlock = 1, PublicList = SQLCODE ]

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.

MINUTE​

ClassMethod MINUTE(timeexp As %Library.String = "") As %Library.Integer [ ProcedureBlock = 1 ]

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.

MONTH​

ClassMethod MONTH(dateexp As %Library.String = "") As %Library.Integer [ ProcedureBlock = 1 ]

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.

MONTHNAME​

ClassMethod MONTHNAME(dateexp As %Library.String = "") As %Library.String [ ProcedureBlock = 1 ]

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.

MVR​

ClassMethod MVR(stringexp As %Library.String = "") As %Library.String [ ProcedureBlock = 1 ]

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

QUARTER​

ClassMethod QUARTER(dateexp As %Library.String = "") As %Library.Integer [ ProcedureBlock = 1 ]

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.

SECOND​

ClassMethod SECOND(timeexp As %Library.String = "") As %Library.Integer [ ProcedureBlock = 1 ]

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.

SQLCODE​

ClassMethod SQLCODE(SQLCODE As %Library.Integer) As %Library.String [ SqlProc ]

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)

COLLATE​

ClassMethod COLLATE(stringexp As %Library.String = "", transSpec As %Library.String = "", maxlen As %Library.Integer) As %Library.String [ ProcedureBlock = 1 ]

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.

SQLSTRING​

ClassMethod SQLSTRING(stringexp As %Library.String = "", maxlen As %Library.Integer) As %Library.String [ ProcedureBlock = 1 ]

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).

SQLUPPER​

ClassMethod SQLUPPER(stringexp As %Library.String = "", maxlen As %Library.Integer) As %Library.String [ ProcedureBlock = 1 ]

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).

STRING​

ClassMethod STRING(stringexp As %Library.String = "", maxlen As %Library.Integer) As %Library.String [ ProcedureBlock = 1 ]

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).

TOCHAR​

ClassMethod TOCHAR(expr As %Library.String = "", format As %Library.String = "") As %Library.String [ ProcedureBlock = 1, PublicList = SQLCODE ]

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.

TODATE​

ClassMethod TODATE(dateexp As %Library.String = "", format As %Library.String = "") As %Library.String [ ProcedureBlock = 1, PublicList = SQLCODE ]

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.

TOPOSIXTIME​

ClassMethod TOPOSIXTIME(stringexp As %Library.String = "", format As %Library.String = "") As %Library.String [ ProcedureBlock = 1, PublicList = SQLCODE ]

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.

TOTIMESTAMP​

ClassMethod TOTIMESTAMP(stringexp As %Library.String = "", format As %Library.String = "") As %Library.String [ ProcedureBlock = 1, PublicList = SQLCODE ]

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.

TRUNCATE​

ClassMethod TRUNCATE(stringexp As %Library.String = "", maxlen As %Library.Integer = 32768) As %Library.String [ ProcedureBlock = 1 ]

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.

UPPER​

ClassMethod UPPER(stringexp As %Library.String = "") As %Library.String [ ProcedureBlock = 1 ]

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.

VECTORCOSINE​

ClassMethod VECTORCOSINE(vec1 As %Library.Vector = "", vec2 As %Library.Vector = "") As %Library.Double [ ProcedureBlock = 1 ]

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.

VECTORDOTPRODUCT​

ClassMethod VECTORDOTPRODUCT(vec1 As %Library.Vector = "", vec2 As %Library.Vector = "") As %Library.Double [ ProcedureBlock = 1 ]

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.

WEEK​

ClassMethod WEEK(dateexp As %Library.String = "") As %Library.Integer [ ProcedureBlock = 1, PublicList = SQLCODE ]

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.

YEAR​

ClassMethod YEAR(dateexp As %Library.String = "") As %Library.Integer [ ProcedureBlock = 1 ]

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.