%SYSTEM.SQL.Functions

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

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.

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.

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

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.

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.

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.

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.

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

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.

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

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)

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

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