Aggregate function: used in a query to return the average of the values in the numfield column. For example, SELECT AVG(Age) FROM Sample.Person. AVG(DISTINCT numfield) averages the number of unique values in the field column. Fields with NULL are ignored.

When expression is a date value string, such as '20041123' and datatype is TIMESTAMP or DATETIME, a time value of '00:00:00' is supplied.

When expression is a time value string, such as '1:35PM' and datatype is TIMESTAMP or DATETIME, the time is converted to a 24-hour clock, the AM or PM suffix is removed, a missing seconds interval is filled in with zeros, and the default date value of '19000101' is supplied. Thus '1:35PM' is converted to '19000101 13:35:00'.

When expression is a date value string, such as '20041123' and datatype is DATE, the date is returned in Caché $HOROLOG date format, such as 60703 (March 14, 2007).

Caché TSQL does not support data type XML. However, instead of generating an error during compilation, CAST(x AS XML) in SQL mode generates CAST(x AS VARCHAR(32767)). In procedure mode, CAST(x AS XML) does not generate any conversion.

Returns the position in target (counting from 1) corresponding to first character of the first occurrence of seekstring. You can use the optional startpoint integer to specify where to begin the search. The return value counts from the beginning of target, regardless of the startpoint. If startpoint is not specified, specified as 0, 1, or as a negative number, target is searched from the beginning. CHARINDEX returns 0 if seekstring is not found.

When datatype is BIT and expression is a boolean value: if the input value is a non-zero number, the result is 1. if the input value is 0, the result is 0. If the input value is the string 'TRUE' (case insensitive), the result is 1. If the input value is the string 'FALSE' (case insensitive), the result is 0. If the input value is NULL, the result is NULL. Any other input value generates an SQLCODE -141 error.

When datatype is datetime or timestamp and expression is a date value string, such as '20041123', a time value of '00:00:00' is supplied. When expression is a time value string, such as '1:35PM' and datatype is datetime or timestamp, the time is converted to a 24-hour clock, the AM or PM suffix is removed, a missing seconds interval is filled in with zeros, and the default date value of '19000101' is supplied. Thus '1:35PM' is converted to '19000101 13:35:00'.

CONVERT supports the DATETIME2 data type. Caché maps DATETIME2 to system-defined DDL mapping %Library.TimeStamp. This mapping is supplied with new installs; if you are using an upgrade install, you may need to create this mapping.

The optional format-code argument is used to specify a date/time format when converting a datetime or timestamp value to a string. By specifying various format codes you can return a dates and times in a variety of different formats. The available format codes are 0 through 14 (100 through 114), 20 & 21 (120 & 121), 126, 130, and 131. The default format-code is 120:

Aggregate function: used in a query to return the count of the values in the field column. Fields with NULL are not counted. For example, SELECT COUNT(Name) FROM Sample.Person. COUNT(*) and COUNT(1) are synonyms, they count all rows. COUNT(DISTINCT field) counts the number of unique values in the field column. Fields with NULL are not counted.

Returns the value of date modified by adding the interval specified in code the num number of times. The date can be a date, time, or date/time string in a variety of formats. You can specify any of the following code values, either the abbreviation (left column) or the name (right column):

yy

Year

qq

Quarter

mm

Month

dy

DayofYear

dd

Day

dw, w

Weekday

wk

Week

hh

Hour

mi

Minute

ss

Second

ms

Millisecond

Code values are not case-sensitive. Day, DayofYear, and Weekday all return the same value.

The value returned by DATEADD always includes both date and time in the format:

yyyy-mm-dd hh:mm:ss.n

Fractional seconds are only returned if the source contained fractional seconds.

If a date is not specified (that is, if date contains only a time value), it defaults to 1/1/1900.

If a time is not specified in date, it defaults to 00:00:00. Hours are always returned based on a 24-hour clock.

Returns the value of the part of the date specified by code as a string. The date can be a date, time, or date/time string in a variety of formats. date must be specified as a quoted string; code permits, but does not require enclosing quotes. Available code values are:

yyyy, yy

year

Year. Returns a four-digit year. If a two-digit year is specified, DATENAME supplies '19' as first two digits.

qq, q

quarter

Quarter. Returns an integer 1 through 4.

mm, m

month

Month. Returns the full name of the month. For example, 'December'.

dy, y

dayofyear

Day of Year. Returns an integer count of days 1 through 366.

dd, d

day

Day of Month. Returns an integer count 1 through 31.

wk, ww

week

Week of Year. Returns an integer count 1 through 53.

dw, w

weekday

Day of Week. Returns the number of the day of the week, counting from Sunday. For example, 3 is Tuesday.

hh

hour

Hour. Returns the hour of the day (24hour clock), an integer 0 through 23.

mi, n

minute

Minute. Returns an integer 0 through 59.

ss, s

second

Second. Returns a decimal number 0 through 59 which may have a fractional part representing milliseconds.

ms

millisecond

Millisecond. Returns the fractional part of a second as an integer.

Code values are not case-sensitive.

If a date is not specified, it defaults to 1/1/1900. Two-digit years default to 19xx.

If a time is not specified, it defaults to 00:00:00. Hours are always returned based on a 24-hour clock. Seconds are always returned with fractional seconds, if fractional seconds are defined. Milliseconds are returned as an integer, not a decimal fraction.

Returns the name of the indexed column in the specified table. table_name can be fully qualified. index_id is the number of the table's index. key is a key in the index, a value between 1 and sysindexes.keycnt (for a clustered index) or sysindexes.keycnt+1 (for a non-clustered index). user_id is parsed but ignored.

Returns a unique value of a type compatible with the SQL Server UNIQUEIDENTIFIER data type. UNIQUEIDENTIFIER is a system-generated 16-byte binary string, also known as a a globally unique ID (GUID). A GUID is used to synchronize databases on occasionally connected systems. A GUID is a 36-character string consisting of 32 hexadecimal numbers separated into five groups by hyphens. Caché TSQL does not support UNIQUEIDENTIFIER; it instead uses VARCHAR(36) as the data type for a Globally Unique ID.

The NEWID function takes no arguments. Note that the argument parentheses are required.

NEWID() can be used to specify the DEFAULT value when defining a field.

Takes the object ID integer and returns the corresponding object name of the specified object. Returns the empty string if id does not exist. Can be used within procedure code or trigger code. The inverse of OBJECT_ID.

Returns an integer specifying the beginning position of the first occurrence of pattern in string, counting from 1. If pattern is not found in string, 0 is returned. Specify pattern as a quoted string. Comparisons are case-sensitive. The pattern can contain the following wildcard characters:

%

Zero or more characters. For example, '%a%' returns the position of the first occurrence of 'a' in string, including 'a' as the first character in string.

_

Any single character. For example, '_l%' returns 1 if string begins with a substring such as 'Al', 'el', and 'il'.

[xyz]

Any single character from the specified list of characters. For example, '[ai]l%' returns 1 if string begins with the substring 'al' or 'il', but not 'el' or 'Al'.

[a-z]

Any single character from the specified range of characters. For example, '%s[a-z]t%' matches 'sat', 'set', and 'sit'. A range must be specified in ascending ASCII sequence.

The caret (^) character is a not a wildcard character; if included within square brackets it is treated as a literal. A pattern commonly consists of a search string enclosed in percent (%) characters '%Chicago%' indicating that the entire string should be searched.

Returns a random number as a fractional number less than 1. The optional seed integer argument is ignored; it is provided for compatibility. If RAND is used more than once in a query it returns different random values.

Finds every instance of the search string in the target string and replaces it with the replace string, and returns the resulting string. To remove the search string from the target string, specify replace as an empty string.

Returns num rounded to the number of decimal digits specified by the integer length. If length is greater than the number of decimal digits, no rounding is performed. If length is 0, num is rounded to an integer. If the length argument is omitted, it defaults to 0. If length is a negative integer, num is rounded to the left of the decimal point. A third argument is not accepted by ROUND.

Returns the last identity value inserted into an IDENTITY column in the same scope. However, the last IDENTITY is not limited to the scope of the current procedure. Therefore, you should only use SCOPE_IDENTITY when you know that a statement within the current procedure has generated an IDENTITY value. For example, SCOPE_IDENTITY should be used after an INSERT command in the same procedure.

The following Dynamic SQL example returns the IDENTITY value from the second INSERT:

Returns a value indicating the sign of num. If num is negative (for example, -32), it returns -1. If num is positive (for example, 32 or +32), it returns 1. If num is zero (for example, 0 or -0), it returns 0.

Returns a string of length characters. If the integer length is equal to or greater than the number of characters in the numeric num (including decimal point and sign characters), STR returns num converted to a string and padded with leading blanks to make the resulting string of length characters.

If the optional integer precision is specified, num is truncated to the specified number of decimal digits before string conversion. If precision is omitted, num is truncated to its integer portion. If precision is larger than the number of decimal digits, num is padded with trailing zeros before string conversion.

If length is omitted, it defaults to 10. If length is less than the number of characters in num (after adjustment by precision) a dummy string consisting of all asterisks of length number of characters is returned.

Returns string with length number of characters removed and the replace string inserted. The point of removal and insertion is specified by the start integer, counting from the beginning of string. If length is 0, no characters are removed. If replace is the empty string, no characters are inserted.

If start is greater than the number of characters in string, no value is returned. If start is 1, length number of characters are removed from the beginning of string and the replace string inserted. If start is 0, length minus 1 number of characters are removed from the beginning of string and the replace string inserted.

Returns the name of the current OS user. Parentheses are required, no argument is permitted. Equivalent to TSQL USER_NAME(), the Caché SQL USER function, and the ObjectScript $USERNAME special variable.

Returns the name of the current OS user. Parentheses are required, no argument is permitted. Equivalent to TSQL USER_NAME(), the Caché SQL USER function, and the ObjectScript $USERNAME special variable.