Home > Class Reference > %SYS namespace > %SYSTEM.SQL.Functions

%SYSTEM.SQL.Functions

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

Methods

classmethod ABS(val As %Library.String = "") as %Library.Float [ Language = objectscript ]
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.
classmethod ALPHAUP(stringexp As %Library.String = "") as %Library.String [ Language = objectscript ]
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 [ Language = objectscript ]
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
classmethod CONVERT(expr As %Library.String = "", convto As %Library.String = "", convfrom As %Library.String = "") as %Library.String [ Language = objectscript ]
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.PosixDate, 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.
classmethod DATE(exp As %Library.String = "") as %Library.Date [ Language = objectscript ]
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),
classmethod DATEADD(datepart As %Library.String = "", val As %Library.Numeric = "", dateexp As %Library.String = "") as %Library.TimeStamp [ Language = objectscript ]
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.
classmethod DATEDIFF(datepart As %Library.String = "", startdate As %Library.String = "", enddate As %Library.String = "") as %Library.Integer [ Language = objectscript ]
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.
classmethod DATENAME(datepart As %Library.String = "", dateexp As %Library.String = "") as %Library.String [ Language = objectscript ]
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.
classmethod DATEPART(datepart As %Library.String = "", dateexp As %Library.String = "") as %Library.Integer [ Language = objectscript ]
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.
classmethod DAYNAME(dateexp As %Library.String = "") as %Library.String [ Language = objectscript ]
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.
classmethod DAYOFMONTH(dateexp As %Library.String = "") as %Library.Integer [ Language = objectscript ]
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.
classmethod DAYOFWEEK(dateexp As %Library.String = "") as %Library.Integer [ Language = objectscript ]
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.
classmethod DAYOFYEAR(dateexp As %Library.String = "") as %Library.Integer [ Language = objectscript ]
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.
classmethod FLOOR(val As %Library.Numeric = "") as %Library.Integer [ Language = objectscript ]
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.
classmethod HOUR(timeexp As %Library.String = "") as %Library.Integer [ Language = objectscript ]
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.
classmethod INSTR(string As %Library.String = "", substring As %Library.String = "", position As %Library.Integer = 1, occurrence As %Library.Integer = 1) as %Library.Integer [ Language = objectscript ]
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.
classmethod LASTDAY(dateexp As %Library.String = "") as %Library.Date [ Language = objectscript ]
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.Posix 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 [ Language = objectscript ]
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.
classmethod MONTH(dateexp As %Library.String = "") as %Library.Integer [ Language = objectscript ]
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.
classmethod MONTHNAME(dateexp As %Library.String = "") as %Library.String [ Language = objectscript ]
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.
classmethod MVR(stringexp As %Library.String = "") as %Library.String [ Language = objectscript ]
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 [ Language = objectscript ]
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.
classmethod SECOND(timeexp As %Library.String = "") as %Library.Integer [ Language = objectscript ]
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.
classmethod SQLCODE(SQLCODE As %Library.Integer) as %Library.String [ Language = objectscript, SQLProc = 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 [ Language = objectscript ]
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 [ Language = objectscript ]
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 [ Language = objectscript ]
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 [ Language = objectscript ]
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.
See the TO_CHAR Documentation in the SQL Reference for complete details.
classmethod TODATE(dateexp As %Library.String = "", format As %Library.String = "") as %Library.String [ Language = objectscript ]
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.

See the TO_DATE Documentation in the SQL Reference for complete details.

classmethod TOPOSIXTIME(stringexp As %Library.String = "", format As %Library.String = "") as %Library.String [ Language = objectscript ]
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.

See the TO_POSIXTIME Documentation in the SQL Reference for complete details.

classmethod TOTIMESTAMP(stringexp As %Library.String = "", format As %Library.String = "") as %Library.String [ Language = objectscript ]
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:MM: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.

See the TO_TIMESTAMP Documentation in the SQL Reference for complete details.

classmethod TRUNCATE(stringexp As %Library.String = "", maxlen As %Library.Integer = 32768) as %Library.String [ Language = objectscript ]
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.
classmethod UPPER(stringexp As %Library.String = "") as %Library.String [ Language = objectscript ]
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.
classmethod WEEK(dateexp As %Library.String = "") as %Library.Integer [ Language = objectscript ]
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.
classmethod YEAR(dateexp As %Library.String = "") as %Library.Integer [ Language = objectscript ]
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.

Inherited Methods

Help()