%SYSTEM.SQL.Functions
abstract class %SYSTEM.SQL.Functions extends %SYSTEM.Help
Method Inventory
- ABS()
- ALPHAUP()
- CEILING()
- COLLATE()
- CONVERT()
- DATE()
- DATEADD()
- DATEDIFF()
- DATENAME()
- DATEPART()
- DATETRUNC()
- DAYNAME()
- DAYOFMONTH()
- DAYOFWEEK()
- DAYOFYEAR()
- FLOOR()
- HOUR()
- INSTR()
- LASTDAY()
- MINUTE()
- MONTH()
- MONTHNAME()
- MVR()
- QUARTER()
- SECOND()
- SQLCODE()
- SQLSTRING()
- SQLUPPER()
- STRING()
- TOCHAR()
- TODATE()
- TOPOSIXTIME()
- TOTIMESTAMP()
- TRUNCATE()
- UPPER()
- VECTORCOSINE()
- VECTORDOTPRODUCT()
- WEEK()
- YEAR()
Methods
$SYSTEM.SQL.Functions.ABS(numeric-expression)
- numeric-expression
- A number whose absolute value is to be returned
Also see the ABS reference page.
- $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.
$SYSTEM.SQL.Functions.CEILING(numeric-expression)
- numeric-expression
- A number whose ceiling is to be calculated
Also see the CEILING reference page.
$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.
$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
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.
$SYSTEM.SQL.Functions.DATE(exp)
- exp
- An expression that is a logical %Library.Date, %Library.TimeStamp, %Library.PosixTime, %Library.Integer, or %Library.String value.
Also see the DATE reference page.
$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}
Also see the DATEADD reference page.
$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.
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:
- Sybase/SQL-Server-date is one of these five formats:
- Sybase/SQL-Server-time represents one of these three formats:
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)
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 (.).
HH:MM[:SS:SSS][{AM|PM}]
HH:MM[:SS.S]
HH['']{AM|PM}
Also see the DATEDIFF reference page.
$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.
- 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:
- Sybase/SQL-Server-date is one of these five formats:
- Sybase/SQL-Server-time represents one of these three formats:
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)
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 (.).
HH:MM[:SS:SSS][{AM|PM}]
HH:MM[:SS.S]
HH['']{AM|PM}
Also see the DATENAME reference page.
$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.
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:
- Sybase/SQL-Server-date is one of these five formats:
- Sybase/SQL-Server-time represents one of these three formats:
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)
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 (.).
HH:MM[:SS:SSS][{AM|PM}]
HH:MM[:SS.S]
HH['']{AM|PM}
Also see the DATEPART reference page.
$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
- 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:
- Sybase/SQL-Server-date is one of these five formats:
- Sybase/SQL-Server-time represents one of these three formats:
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)
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 (.).
HH:MM[:SS:SSS][{AM|PM}]
HH:MM[:SS.S]
HH['']{AM|PM}
$SYSTEM.SQL.Functions.DAYNAME(dateexp)
- dateexp
- An expression that is a logical %Library.Date, %Library.TimeStamp, or %Library.PosixTime value.
Also see the DAYNAME reference page.
$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.
$SYSTEM.SQL.Functions.DAYOFWEEK(dateexp)
- dateexp
- An expression that is a logical %Library.Date value, %Library.TimeStamp, %Library.PosixTime, or an $Horolog value.
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.
$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.
$SYSTEM.SQL.Functions.FLOOR(dateexp)
- numeric-exp
- A number whose floor is to be calculated.
Also see the FLOOR reference page.
$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.
$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.
$SYSTEM.SQL.Functions.LASTDAY(dateexp)
- dateexp
- A %Library.Date, %Library.TimeStamp, or %Library.PosixTime logical value.
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.
$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.
$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.
$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.
$SYSTEM.SQL.Functions.MVR(stringexp)
- stringexp
- Any string expression value.
$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.
$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.
Parameters:
- SQLCODE
- SQLCODE value.
Examples:
- Write $SYSTEM.SQL.Functions.SQLCODE(-105)
- Write $SYSTEM.SQL.Functions.SQLCODE(100)
$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).
$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.
$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.
$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 second use of TOCHAR is to convert a number to a string.
Also see the TO_CHAR reference page.
$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.
Also see the TODATE reference page.
$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.
Also see the TO_POSIXTIME reference page.
$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.
Also see the TO_TIMESTAMP reference page.
$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.
$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.
$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.
$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.
$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.
$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.