The following descriptions of the entries shown in the previous syntax diagram are listed in alphabetical order, except for the description of expression, described first because of its multiple occurrences in the diagram. Also, see Rules for Scalar Functions.
Use the CHAR function to obtain a string representation of a date/time value. The result is a fixed-length character string. Its first argument must be a date, time, or timestamp. Its second argument is used, when the first argument is a date or time, to specify an ISO, USA, EUR, or JIS string format.
If the first argument is a date, the result has a length of 10 and is the character string representation of the date in the format specified by the second argument.
If the first argument is a time, the result has a length of eight and is the character string representation of the time in the format specified by the second argument.
If the first argument is a timestamp, the result has a length of 26 and is the character string representation of the timestamp. Do not specify a second argument (for a string format) when the first argument is a timestamp.
See Character String Literals for information about date, time, and timestamp formats.
Specifies International Standards Organization format.
Specifies International USA Standard format.
Specifies IBM European Standard format.
Specifies Japanese Industrial Standard format.
See Character Functions
Use the DATE function to obtain a date from a value. The result is a date. Its argument must be a date, timestamp, a string representation of a date, a character string of length 7, or a positive number.
For example, DATE(TIMESTAMP('1989-03-20-11.30.00') + 2 DAYS) results in a date of 1989-03-22 (in ISO or JIS format).
If its argument is a character string of length seven, it is assumed to have the form yyyynnn where yyyy is the year and nnn is the day within the year in the range of 001 to 366.
For example, DATE('1989079') results in a date of 1989-03-20 (in ISO or JIS format).
If the argument is a positive number, n, the result is the date that is n days after December 31, 0000.
For example, DATE(32) results in a date of 0001-02-01 (in ISO or JIS format).
Use the DAY function to obtain the day part of a value. The result is an integer representing a day. The sign of the result is negative only if the value of its expression is a negative duration. Its argument must be a date, timestamp, or DECIMAL(8,0) number interpreted as a date-duration.
For example, if BIRTHDATE is March 25, 1945, that is to say, 19450325, then DAY(BIRTHDATE) results in 25.
Use the DAYS function to obtain an integer representation of a date. The result is an integer representing the number of days since December 31, 0000 (that is to say, January 1 is 1 day, February 1 is 32 days, and so on). The sign of the result is always positive. Its argument can be a date, timestamp, or string representation of a date.
For example, DAYS('0001-02-01') results in 32 days.
Use the DECIMAL function to obtain a decimal representation of a numeric value. Three arguments are possible. The first argument is required. The second and third arguments (labeled as "integer" in the syntax diagram) are optional. The result is a decimal number with a precision of p and a scale of s, where p is the second argument and s is the third argument.
The first argument must be a number. If you specify a second argument, it represents the precision p and must be an integer in the range of 1 to 31. If you specify a third argument, it represents the scale s and must be an integer in the range of 0 to the p specified in the second argument. You cannot specify a third argument if you have not specified a second argument.
Omitting the third argument results in a value of 0 for the scale. Omitting the second argument results in:
The result can be null if the first argument can be null; the result is the null value if the first argument is null. The result is the same number that would occur if the first argument were assigned to a decimal column or variable with a precision of p and a scale of s.
If the number of significant decimal digits required to represent the whole part of the number is greater than p-s, an error occurs.
For example, if SALARY is a FLOAT column, DECIMAL(AVG(SALARY),8,2) results in the average salary being converted to a packed decimal value of xxxxxx.xx.
Use the DIGITS function to obtain a fixed-length character string representation of a number. Its argument must be an integer or a decimal number. The string of digits that make up the result represent the absolute value of the argument without regard to its scale. The result therefore does not include a sign or a decimal point. Leading zeros are included in the result as necessary so that length of string equals:
The result can be null if the argument can be null; the result is the null value if the argument is null.
For example, if the data type of COLUMNX is DECIMAL(6,2), and if COLUMNX has a value of -7.27, then DIGITS(COLUMNX) gives '000727' as the result.
Enter the expression which is to be the argument of the function. For more information about expressions, see Expressions.
Use the FLOAT function to obtain a floating-point representation of a number. Its argument must be a number. A double precision floating-point number is the result. The result can be null if the argument can be null; the result is the null value if the argument is null.
For example, if ACSTAFF is an INTEGER column, FLOAT(ACSTAFF)/2 results in the double precision floating-point representation of half of the value in ACSTAFF.
Use the HEX function to obtain an hexadecimal representation of a value. A character string is the result of the function. The result can be null if the argument can be null; the result is the null value if the argument is null.
In the string of hexadecimal digits that form the result, the first two digits represent the first byte of the argument, the second two digits the second byte, and so on. If a date or time value is the argument, the result is the hexadecimal representation of the internal form of the argument.
The length of the result is twice the defined (maximum) length of the argument.
If the argument is not a varying-length string and the length of the result is less than 255, the result is a fixed-length string. Otherwise, the result is a varying-length string whose maximum length depends on the following considerations. If the argument:
For example, if 'ABC' is contained in CHAR column COLX, HEX(COLX) results in the fixed-length string 'C1C2C3'.
Use the HOUR function to obtain the hour part of a value. The result is an integer representing an hour. The sign of the result is negative only if the value of its argument is a negative duration. Its argument must be a time, timestamp, or a DECIMAL(6,0) number interpreted as a time-duration.
For example, if TIME1 is a timestamp of 19890203093020009900, then HOUR(TIME1) results in 9.
Use the INTEGER function to obtain an integer representation of a number. The argument must be a number. A large integer is the result of the function. The result can be null if the argument can be null; the result is the null value if the argument is null.
The result obtained by this function is the same number that would occur if the argument were assigned to a large integer column or variable. An error occurs if the whole part of the argument is not within the range of integers.
For example, if PAYNUM is a DECIMAL(8,2) column, INTEGER(SUM(PAYNUM)+.5) results in the sum (rounded up) as an integer value.
Use the LENGTH function to obtain the length of a value. Any value can be used as the argument. The result is a large integer. The result can be null if the argument can be null. The result is the null value if the argument is null.
The length of the argument is the result. The null indicator byte of column arguments that allow null values is not included in the length. Blanks are included in the length of strings, but the length control field of varying-length strings is not included in the length. The actual (not the maximum) length of varying-length strings is the length.
The length is the number of bytes used to represent the value as follows:
For example, if COLX is a VARCHAR(20) column, LENGTH(COLX) returns the actual length of the string in that column.
Use the MICROSECOND function to obtain the microsecond part of a value. The result is an integer representing a number of microseconds. The sign of the result is always positive. Its argument must be a timestamp.
For example, if TIME1 is 19890320093020109000 then, MICROSECOND(TIME1) results in 109000 microseconds.
Use the MINUTE function to obtain the minute part of a value. The result is an integer representing a minute. The sign of the result is negative only if the value of its argument is a negative duration. Its argument must be a time, timestamp, or a DECIMAL(6,0) number interpreted as a time-duration.
For example, if TIME1 is a timestamp of 19890203093020009900, then MINUTE(TIME1) results in 30.
Use the MONTH function to obtain the month part of a value. The result is an integer representing a month. The sign of the result is negative only if the value of its expression is a negative duration. Its argument must be a date, timestamp, or DECIMAL(8,0) number interpreted as a date-duration.
For example, if BIRTHDATE is of March 25, 1945, that is to say, 19450325, then MONTH(BIRTHDATE) results in 3.
Use the SECOND function to obtain the seconds part of a value. The result is an integer representing a second. The sign of the result is negative only if the value of its argument is a negative duration. Its argument must be a time, timestamp, or a DECIMAL(6,0) number interpreted as a time-duration.
For example, if TIME1 is a timestamp of 19890203093020009900, then SECOND(TIME1) results in 20.
Use the SUBSTR function to obtain a substring of a string. Three arguments are possible: string, start, and length. The string and start arguments are required, but the length argument is optional. The result can be null if any of the arguments can be null. The result is the null value if any of the arguments are null.
Note: The SUBSTR function accepts mixed data strings. However, because SUBSTR operates on a strict byte-count basis with character strings, the result is not necessarily a properly formed mixed data string.
DBCS characters can be used in either GRAPHIC or CHAR with MIXED DATA. When GRAPHIC is used, there is no problem because there are no shift-out or shift-in bytes, and start and length refer to double-byte characters (not bytes). However, be aware that in CHAR MIXED DATA, the shift-out and shift-in may not balance in the result string.
You can use the VARGRAPHIC scalar function to normalize data before doing comparisons.
Refers to an expression specifying the string from which the result is derived. The string must be a character string or a graphic string. If string is a character string, the result of the function is a character string. If it is a graphic string, the result of the function is a graphic string.
A substring of the string argument is 0 or more contiguous characters of the string argument.
Refers to an expression specifying the position of the first character of the result. The start argument must be a positive binary integer that is not greater than the length attribute of the string argument. Note that the length attribute of a varying-length string is its maximum length.
Refers to an expression specifying the length of the result. If you specify the length argument, it must be a binary integer in the range of 0-n, where n is the length attribute of the string argument minus the start argument plus 1, with the exception that n must not be the integer constant 0.
If you explicitly specify the length argument, the string argument is effectively padded on the right with the necessary number of blank characters so that the specified substring of the string argument always exists. The length argument has a default of the number of characters from the character specified by the start argument to the last character of the string argument, but if the string argument is a varying-length string with a length that is less than the start argument, the default is 0 and the result is the empty string.
If you explicitly specify the length argument to be an integer constant of less than 255, the result is a fixed-length string. If you do not explicitly specify the length argument but the string argument is a fixed-length string and the start argument is an integer constant, the result is a fixed-length string. In all other cases, the result is a varying-length string with a maximum length that is the same as the length attribute of the string argument.
If the string argument is a fixed-length string, omitting the length argument is an implicit specification of
LENGTH(string) - start + 1
If the string argument is a varying-length string, omitting the length argument is an implicit specification of either
LENGTH(string) - start + 1
or 0, whichever is greater.
For example, if FIRSTNAME is a VARCHAR(20) column, SUBSTR(FIRSTNAME,1,1) results in a fixed-length string value containing the first character of the value in FIRSTNAME.
Use the TIME function to obtain the time from a value. The result is a time. Its argument must be a time, timestamp, a string representation of a time. For example if TIME1 is 10890320093020, then TIME(TIME1) results in time 9.30.20 (in ISO or EUR format).
Use the TIMESTAMP function to obtain a timestamp from a value or a pair of values. The result is a timestamp. If only one argument is given, it must be a timestamp, a string representation of a timestamp, a character string of length 8, or a character string of length 14. If the value is a character string of length 8, it is assumed to be a STORE CLOCK value representation of a timestamp. If the value is a character string of length 14, it must be in the form yyyymmddhhmmss where yyyy is the year, mm the month, dd the day, hh the hours, mm the minutes, and ss the seconds.
If a second optional argument is specified, the second argument must be a time or a string representation of a time, and the first argument must be a date or a string representation of a date.
For example, TIMESTAMP(CURRENT DATE, '11.30.00') results in the timestamp 11:30 today.
Use the VALUE function to substitute a value for the null value. The arguments' data types must be compatible. Because character strings are not converted to date/time values, all arguments must be dates if any argument is a date. Similarly, all arguments must be times if any argument is a time, all must be timestamps if any are timestamps, and all must be character strings if any are character strings.
Arguments are evaluated in the order of specification. The result of the function is equal to the first argument that is not null. The result can only be null if all arguments can be null. The result is the null value only if all arguments are null.
The result is defined as equal to an argument because that argument is converted or extended, if necessary, in order to conform to the data type of the function. The data type of the result is derived from the data types of the specified arguments as follows:
If any argument is a varying-length string, the result is a varying-length string whose maximum length is equal to the longest string that can result from the application of the function.
If all arguments are fixed-length strings, the result is a fixed-length string whose length is equal to the longest string that can result from the application of the function.
The result is a date if the arguments are dates, times if the arguments are times, or timestamps if the arguments are timestamps.
If the arguments are numbers, the result is the numeric data type that would occur if all arguments were part of a single arithmetic expression. If that data type is decimal, it has a precision of p and a scale of s. Therefore, s is the largest result scale of any argument and p is the minimum of 31 and s + n, where n is the largest integral part result of any argument. Conversion errors are possible if the sum of s + n is greater than 31.
For example, SALARY + VALUE(COMMISSION,0) results in the sum of SALARY and COMMISSION if COMMISSION is not the null value, or the sum of SALARY and 0 if COMMISSION contains the null value.
Use the VARGRAPHIC function to convert SBCS (Single-Byte Character Set) and MIXED strings to VARGRAPHIC.
In SQL, string comparisons are strictly byte-for-byte, so predicates involving MIXED strings may not give the desired results. For instance, a string with the SBCS version of XYZ does not compare as equal to a string with a Shift-Out, then the DBCS version of XYZ, then a Shift-In. You can use the VARGRAPHIC scalar function to normalize both strings into VARGRAPHIC data types before doing the comparison.
When SBCS strings are converted, each SBCS character is paired with a X'42' to form the double-byte character. In MIXED strings, the SBCS characters are converted, and Shift-In and Shift-Out characters are removed. FOR BIT DATA operands are not allowed.
Use the YEAR function to obtain the year part of a value. The result is an integer representing a year. The sign of the result is negative only if the value of its expression is a negative duration. Its argument must be a date, timestamp, or DECIMAL(8,0) number interpreted as a date-duration.
For example, if BIRTHDATE is March 25, 1945, that is to say, 19450325, then YEAR(BIRTHDATE) results in 1945.
|
Copyright © 2014 CA.
All rights reserved.
|
|