Name | Operand Type | Result Type | Description |
|---|---|---|---|
c(expr) | any | c | Converts any value to a c string |
char(expr) | any | char | Converts any value to a char string |
date(expr) ingresdate(expr) | c, char, text, varchar | date | Converts a c, char, varchar, or text string to internal date representation |
decimal(expr, 1 < p <31 0 < s <p) | c, char, varchar, text, float, money, integer(1), smallint, integer | decimal | Returns the decimal representation of the argument string |
dow(expr) | date | c | Converts an absolute date into its day of week (for example, 'Mon,' 'Tue'); the result length is 3 |
float4(expr) | c, char, varchar, text, float, money, integer(1), smallint, integer | float4 | Converts the specified expression to float4 |
float8(expr) | c, char, varchar, text, float, money, integer(1), smallint, integer | float | Converts the specified expression to float |
hex(expr) | varchar, c, char, text | varchar | Returns the hexadecimal representation of the argument string; the length of the result is twice the length of the argument, because the hexadecimal equivalent of each character requires two bytes For example, hex('A') returns '61' (ASCII) or 'C1' (EBCDIC). |
int1(expr) tinyint(expr) | c, char, varchar, text, float, money, integer(1), smallint, integer | integer1 | Converts the specified expression to integer1; floating point values are truncated |
int2(expr) smallint(expr) | c, char, varchar, text, float, money, integer(1), smallint, integer | smallint | Converts the specified expression to smallint; floating point values are truncated |
int4(expr) integer(expr) int(expr) | c, char, varchar, text, float, money, integer(1), smallint, integer | integer | Converts the specified expression to integer; floating point values are truncated |
int8(expr) bigint(expr) | c, char, varchar, text, float, money, decimal, integer1, smallint, integer, boolean | bigint | Converts the expression to bigint. Decimal, floating point, and string values are truncated by discarding the fractional portion of the argument. Numeric overflow occurs if the argument is too large for the result type. |
money(expr) | c, char, varchar, text, float, integer(1), smallint, integer, | money | Converts the specified expression to internal money representation; rounds floating point values, if necessary |
nvarchar(expr [, len]) | any | nvarchar | Converts argument to nvarchar Unicode string. If the optional length argument is specified, the function returns the leftmost len characters. Len must be a positive integer value. If len exceeds the length of the expr string, the varying length is set to match the character length of the expr. |
text(expr) | any | text | Converts any value to a text string; this function removes any trailing blanks from c or char string expressions |
to_char(datetime [,format]) | ansidate, timestamp, timstamp with time zone, or timstamp with local time zone | varchar | Converts a datetime or interval value to a value of varchar type in the specified date format. If you omit format, then date is converted to a varchar value as follows: • Ansidate values are converted to values in the default date format. • Timestamp and timestamp with local time zone values are converted to values in the default timestamp format. • Timestamp with time zone values are converted to values in the default timestamp with time zone format. |
Format can be: punctuation "text"--Preserves literal punctuation and quoted text AD, A.D--AD indicator AM, A.M., PM, P.M.--AM/PM indicator CC--Century D--Number of day of week 1 - 7 DAY--Day name padded to suitable width to retain column DD--Day of month 1 - 31 DDD--Day of year 1 - 366 DY--Day name in 3 characters E, EE--Era name FF [1...9]--Fractional seconds FM--Toggle whether formats have leading or trailing blanks FX--Toggle whether exact matching is required HH--Hour od day 1 - 12 HH12--Hour of day 1 - 12 HH24--Hour of day 0 - 23 IW--ISO week of year 1 - 53 IYYY, IYY, IY, I--Last 4, 3, 2, 1 digits of ISO year | |||
J--Julian day (days since January 1, 4712 BC) MI--Minutes 0 - 59 MM--Month 01 - 12 MON--First 3 characters of month name MONTH--Month name padded to suitable length Q--Quarter 1 - 4 RM--Month in Roman numerals RR--Round year in 2 digits RRRR--Round year in 2 or 4 digits SS--Seconds since midnight 1 - 86399 TZH--Time zone hour WW--Week of year 1 - 53 W--Week of month 1 - 5 X--Decimal Y,YYY--Year with comma in this position YEAR--Year spelled out YYYY, YYY, YY, Y--Last 4, 3, 2, or 1 digits of year The case and punctuation of the format are important. Formats whose results are alphabetic follow the case of the format. For example, 'DY' will yield 'MON' but 'Dy' will yield 'Mon'. SELECT TO_CHAR('2013-07-30 23:42:00.290533-07:00','YEAR-MONTH-DAY HH24:MI:SSXFF6 tzh:tzm'); returns 2013-JULY-TUESDAY 23:42:00.290533 -07:00 SELECT TO_CHAR(SYSDATE,'YYYY-MON-DD'); returns 2013-JUL-30 SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'); returns 2013-07-30 23:36:19 | |||
unhex(expr) | any | varchar | Returns the opposite of the hex function. For example, unhex('61626320') returns 'abc ' and unhex('01204161') returns '\001Aa'. Exceptions can occur when a "c" data type suppresses the display of certain stored characters, or when the output data type differs from the input type. Normally one character is generated for every two hex digits being converted to a printable character. If the hex digit pair being converted does not translate to a printable character, the value is converted to a backslash (\), followed by the numeric value of the hex digit pair as a three-digit octal value. |
varchar(expr) | any | varchar | Converts any value to a varchar string; this function removes any trailing blanks from c or char string expressions |
Numeric Function Name | Operand Type | Result Type | Description |
|---|---|---|---|
ABS(n) | all numeric types and money | same as n | Absolute value of n. |
ACOS(n) | all numeric types | float | Arccosine of cosine value n |
ASIN(n) | all numeric types | float | Arcsine value of sine value n |
ATAN(n) | all numeric types | float | Arctangent of n; returns a value from (-pi/2) to pi/2. |
ATAN2(x, y) | all numeric types | float | Arctangent of angle defined by coordinate pair (x, y) |
CEIL(n) CEILING(n) | all numeric types | decimal | Returns the smallest integer greater than or equal to the specified numeric expression. |
COS(n) | all numeric types | float | Cosine of n; returns a value from -1 to 1. |
EXP(n) | all numeric types and money | float | Exponential of n. |
FLOOR(n) | all numeric types | decimal | Returns the largest integer less than, or equal to, the specified numeric expression. |
LOG(n) LN(n) | all numeric types and money | float | Natural logarithm of n. |
MOD(n,b) | integer, smallint, integer1, decimal | same as b | n modulo b. The result is the same data type as b. Decimal values are truncated. |
PI() | None | float | Value of pi (ratio of the circumference of a circle to its diameter) |
ROUND(n,i) | all numeric types | decimal | Rounds value at the i'th place right or left of the decimal, depending on whether i is greater or less than 0. |
SIGN(n) | all numeric types | integer | -1 if n < 0, 0 if n = 0, +1 if n > 0 |
SIN(n) | all numeric types | float | Sine of n; returns a value from -1 to 1. |
SQRT(n) | all numeric types and money | float | Square root of n. |
TAN(n) | all numeric types | float | Tangent value of angle n |
TRUNC(x ,y) | all numeric types | decimal | Truncates x at the decimal point, or at y places to the right or left of the decimal, depending on whether y is greater or less than 0. |
String Function Name | Result Type | Description |
|---|---|---|
ASCII(v1) | any character type | Returns the character equivalent of the value v1, which is an expression of either character or numeric type. |
CHAREXTRACT(c1,n) | varchar or nchar | Returns the nth character or code point of c1. If n is larger than the length of the string, the result is a blank character. It does not support long varchar or long nvarchar arguments. |
CHARACTER_LENGTH(c1) | integer | Returns the number of characters in c1 without trimming blanks, as is done by the LENGTH() function. This function does not support nchar and nvarchar arguments. |
CHR(n) | character | Converts integer into corresponding ASCII code. If n is greater than 255, the conversion is performed on n mod 256. |
CONCAT(c1,c2) | any character or Unicode data type, byte | Concatenates one string to another. The result size is the sum of the sizes of the two arguments. If the result is a c or char string, it is padded with blanks to achieve the proper length. To determine the data type results of concatenating strings, see the table regarding results of string concatenation. This function does not support long nvarchar arguments. |
GENERATE_DIGIT('scheme', 'c1') | varchar(2) | Generates the check digit for the string c1 using the specified encoding scheme. Supported schemes are described under VALIDATE_DIGIT. The check digit is computed from the other characters in the string. Although the majority of check digits are a single character, some encoding schemes may return a two character check digit. Typically the check digit is appended to the string for validation, but this is not the case in all encoding schemes. This function does not append the check digit to the string. |
The check digit can be used to help determine if a string has been entered correctly. A check digit may be able to detect several types of errors, including: • Mistyped characters ('0123' not '0124') • Omissions or additions of characters • Adjacent transposition errors ('01234' not '01243') • Twin errors ('11' becomes '22') • Jump transpositions (123 becomes 321) • Jump twin errors (121 becomes 323) • Phonetic errors—the mixing of similar sounding numbers (sixty '60' not sixteen '16') | ||
A check digit improves the chance of detecting errors in keying, but is not an infallible error detector. Furthermore, a check digit does not provide error correction capability. There are many check digit schemes, each with its own purpose, capabilities, and complexity. For example, the Luhn algorithm is widely used in credit card numbers and is programmatically simple to implement. The Verhoeff algorithm, in contrast, is better at detecting certain error types such as jump transpositions, but is more complicated. The scheme name is case insensitive. When specifying the LUHN or VERHOEFF scheme, the function can take as input a character string of any length. The other schemes are fixed length, so the input string must be of the correct length. This function does not support nchar and nvarchar arguments. | ||
INITCAP(c1) | any character or Unicode data type | Converts all initial characters in c1 to upper case. SELECT INITCAP('This is the final version (VERSION:5.a;6) of Leonard''s will') returns 'This Is The Final Version (Version:5.A;6) Of Leonard's Will' |
LEFT(c1,len) | any character or Unicode data type | Returns the leftmost len characters of c1. If the result is a fixed-length c or char string, it is the same length as c1, padded with blanks. The result format is the same as c1. This function does not support long nvarchar arguments. |
LENGTH(c1) | smallint (for long varchar, returns 4-byte integer) | If c1 is a fixed-length c or char string, returns the length of c1 without trailing blanks. If c1 is a variable-length string, returns the number of characters actually in c1. |
LOCATE(c1,c2) | smallint | Returns the location of the first occurrence of c2 within c1, including trailing blanks from c2. The location is in the range 1 to size(c1). If c2 is not found, the function returns size(c1) + 1. The function size() is described below, in this table. If c1 and c2 are different string data types, c2 is coerced into the c1 data type. This function does not support long varchar or long nvarchar arguments. |
LOWERCASE(c1) or LOWER(c1) | any character or Unicode data type | Converts all upper case characters in c1 to lower case. |
LPAD(expr1, n [, expr2]) | any character type | Returns character expression of length n in which expr1 is prepended by n-m blanks (where m is length(expr1)) or, if expr2 is coded, enough copies of expr2 to fill n-m positions at the start of the result string. SELECT LPAD ('Company',20, '-') returns '-------------Company' |
LTRIM(expr) | any character data type | Returns character expression with leading blanks removed. |
OCTET_LENGTH(c1) | integer | Returns the number of 8-bit octets (bytes) in c1 without trimming blanks, as is done by the LENGTH() function. |
PAD(c1) | text, varchar, or nvarchar | Returns c1 with trailing blanks appended to c1. For example, if c1 is a varchar string that could hold 50 characters but only has two characters, then PAD(c1) appends 48 trailing blanks to c1 to form the result. |
RIGHT(c1,len) | any character or Unicode data type | Returns the rightmost len characters of c1. Trailing blanks are not removed first. If c1 is a fixed-length character string, the result is padded to the same length as c1. If c1 is a variable-length character string, no padding occurs. The result format is the same as c1. This function does not support long nvarchar arguments. |
RPAD(expr1, n [, expr2]) | any character type | Returns character expression of length n in which expr1 is appended by n-m blanks (where m is length(expr1)) or, if expr2 is coded, enough copies of expr2 to fill n-m positions at the end of the result string. SELECT RPAD('Company',12, '-') returns 'Company-----' SELECT RPAD('Company',12, '-x') returns 'Company-x-x-' |
RTRIM(expr) | any character data type | Returns character expression with trailing blanks removed. |
SHIFT(c1,nshift) | any character or Unicode data type | Shifts the string nshift places to the right if nshift > 0 and to the left if nshift < 0. If c1 is a fixed-length character string, the result is padded with blanks to the length of c1. If c1 is a variable-length character string, no padding occurs. The result format is the same as c1. This function does not support long varchar or long nvarchar arguments. |
SIZE(c1) | smallint | Returns the declared size of c1 without removal of trailing blanks. |
SOUNDEX(c1) | any character data type | Returns a c1 four-character field that can be used to find similar sounding strings. For example, SMITH and SMYTHE produce the same SOUNDEX code. If there are less than three characters, the result is padded by trailing zero(s). If there are more than three characters, the result is achieved by dropping the rightmost digits. This function is useful for finding like-sounding strings quickly. A list of similar sounding strings can be shown in a search list rather than just the next strings in the index. This function does not support long varchar or Unicode arguments. |
SOUNDEX_DM(c1) | any character type | Returns one or more six-character codes in a comma-separated varchar string (up to a maximum of 16 codes) that can be used to find similar sounding names. This Daitch-Mokotoff soundex function returns one or more six-character codes in a comma-separated varchar string (up to a maximum of 16 codes) that can be used to find similar sounding names. For example: the codes 739460,734600 may be returned for the name Peterson. Leading and embedded spaces are ignored in the input. The input strings are terminated by non-alphabetic characters other than period (.), apostrophe ('), or hyphen (-). The function ignores the first occurrence of any of these characters, but terminates the string on a subsequent occurrence. This allows the input of names or place names with standard punctuation marks. For example: O'Brien is treated as 'OBRIEN' St.Kilda is treated as 'STKILDA' St. Hilda is treated as 'STHILDA' 'Smyth-Brown' is treated as 'SMYTHBROWN' An empty name, or a name with characters unable to be encoded, returns a code of 000000 and no error is generated. This function does not support long varchar or Unicode arguments. For more information, see SOUNDEX_DM vs. SOUNDEX:). |
SQUEEZE(c1) | text or varchar | Compresses white space. White space is defined as any sequence of blanks, null characters, newlines (line feeds), carriage returns, horizontal tabs and form feeds (vertical tabs). Trims white space from the beginning and end of the string, and replaces all other white space with single blanks. This function is useful for comparisons. The value for c1 must be a string of variable‑length character string data type (not fixed-length character data type). The result is the same length as the argument. This function does not support long varchar or long nvarchar arguments. |
SUBSTR(c1, loc [, len]) | varbyte, varchar, nvarchar, long variants | Returns part of c1 starting at the loc position and either extending to the end of the string or for the number of characters/code points in the len operand. If len is specified and is less than 1, SUBSTR returns NULL. The loc parameter determines the start of the substring to be extracted. If loc is less than 0 the position is counted backwards from the end of c1; if loc is greater than 0 the position is counted from the beginning. If loc is 0 the start position is the first character. After the start of the substring is determined, len characters are extracted. If len is omitted, the rest of c1 is implied. If c1 is varchar, char, c, or text, the result is a varchar. If c1 is nchar or nvarchar, the result is an nvarchar. If c1 is a byte or varbyte, the result is a varbyte. If c1 is passed as a long data type, the result is of the same format. SELECT SUBSTR('Company 2012',9,2) returns '20' SELECT SUBSTR('Company 2012',9) returns '2012' SELECT SUBSTR('Company 2012',-9,4) returns 'pany' |
TRIM(c1) | text or varchar | Returns c1 without trailing blanks. The result has the same length as c1. This function does not support long varchar or long nvarchar arguments. |
UPPERCASE(c1) UPPER(c1) | any character data type | Converts all lower case characters in c1 to upper case. |
VALIDATE_DIGIT('scheme', c1) | integer | Validates that the check digit in the string c1 is mathematically correct for the specified scheme. The function returns 1 for valid and 0 for invalid. Restrictions on scheme names and strings are the same as for GENERATE_DIGIT. You must not include separators, such as dashes or whitespace, in the string. The VALIDATE_DIGIT function does not enforce any internal formatting rules of the scheme. For example, an EAN is split into a GS1 Prefix, the Company Number, an Item reference, and the check digit. This function only calculates the check digit. |
The scheme for GENERATE_DIGIT and VALIDATE_DIGIT functions is case insensitive and can be one of the following: • EAN - European Article Number. Synonyms: EAN_13, GTIN, GTIN_13, JAN. The EAN is a bar-coding standard defined by the standards organization GS1. It is used worldwide for marking retail goods. It was developed as a superset of the original 12-digit Universal Product Code (UPC) system. It is also called a Japanese Article Number (JAN) in Japan. UPC, EAN, and JAN numbers are collectively called Global Trade Item Numbers (GTIN). An EAN string is a 13-character string composed entirely of digits. The check digit is the rightmost character in the string. | ||
• EAN_8 - European Article Number. Synonyms: GTIN_8, RCN_8. The EAN_8 is an 8-character number derived from the longer EAN_13 and is intended for use on small packages where the longer number would be awkward. EAN_8 can also be used internally by companies to identify restricted or "own-brand" products to be sold within their stores only. These are often referred to with the synonym RCN_8. An EAN_8 string is an 8-character string composed entirely of digits. The check digit is the rightmost character in the string. • ISBN - International Standard Book Number. The standard ISBN is a 10-character alphanumeric identifier for printed material. The check digit is the rightmost character and can be either a digit or the alpha 'X'. The other characters in the string must be digits. • ISBN_13 - International Standard Book Number. ISBN-13 is the 13-character version of the ISBN. The string is composed entirely of digits. The check digit is the rightmost character in the string. • ISSN - International Standard Serial Number. The ISSN is an 8-character alphanumeric identifier for electronic or print periodicals. The check digit is the rightmost character and can be either a digit or the alpha 'X'. The other characters in the string must be digits. | ||
• LUHN - The LUHN algorithm. Most credit cards and many government identification numbers are based on this simple algorithm created by Hans Peter Luhn. For example, the Canadian Social Security number is a nine digit number based on the Luhn algorithm. The string can be of any permitted length, but must be composed entirely of digits. The check digit is the rightmost character in the string. • LUHN_A - The LUHN algorithm (alphanumeric). LUHN_A is an extension of the Luhn algorithm that allows for alphabetic characters by assigning numbers 10 to 'A', 11 to 'B', ...35 to 'Z'. It is case insensitive. The Committee on Uniform Security Identification Procedures (CUSIP) provides a 9-character alphanumeric string based on this algorithm. Also the International Securities Identification Number (ISIN) is a 12-character version of this algorithm. The string can be of any permitted length, but must be composed entirely of alphanumeric characters. The check digit is the rightmost character in the string. • UPC - Universal Product Code. Synonyms: UPC_A, EAN_12. Used to mark retail goods as per the EAN (see above). The UPC is composed of 12 digits with the check digit being the rightmost character. • UPC_E - Universal Product Code. The UPC_E is composed of six digits and is intended for smaller packages where a full size UPC_A would be cumbersome. Unlike the UPC_A, the check digit is not appended to the number, but is used to determine the "odd/even" parity assigned to the numbers for when they are encoded as barcode lines. | ||
• VERHOEFF - The Verhoeff algorithm. This algorithm detects all transposition errors and other types of errors that are not detected by the Luhn algorithm. Like the Luhn algorithm, it can handle strings of any permitted length. The string must be composed entirely of digits and the check digit will be the rightmost character. • VERHOEFFNR - Verhoeff algorithm as described in Numerical Recipes in C. A variation on the Verhoeff algorithm published in Numerical Recipes in C by Press, Teukolsky, Vetterling, and Flannery. |
Name | SOUNDEX Code | SOUNDEX_DM Code |
|---|---|---|
Nichols | N242 | 658400,648400 |
Nicholson | N242 | 658460,648460 |
Name | SOUNDEX Code | SOUNDEX_DM Code |
|---|---|---|
Schwartsenegger | S632 | 479465 |
Shwarzenegger | S625 | 479465,474659 |
Schwarzenegger | S625 | 479465,474659 |
1st String | 2nd String | Trim Blanks | Result Type | |
|---|---|---|---|---|
from 1st? | from 2nd? | |||
c | c | Yes | -- | C |
c | text | Yes | -- | C |
c | char | Yes | -- | C |
c | varchar | Yes | -- | C |
text | c | No | -- | C |
char | c | Yes | -- | C |
varchar | c | No | -- | C |
text | text | No | No | text |
text | char | No | Yes | text |
text | varchar | No | No | text |
char | text | Yes | No | text |
varchar | text | No | No | text |
char | char | No | -- | char |
char | varchar | No | -- | char |
varchar | char | No | -- | char |
varchar | varchar | No | No | varchar |
nchar | nchar | No | No | nchar |
nchar | nvarchar | No | No | nchar |
nvarchar | nchar | No | No | nchar |
nvarchar | nvarchar | No | No | nvarchar |
Date Portion | How Specified |
|---|---|
Second | SECOND, SECONDS, SES, SECS |
Minute | MINUTE, MINUTES, MIN, MINS |
Hour | HOUR, HOURS, HR, HRS |
Day | DAY, DAYS |
Week | WEEK, WEEKS, WK, WKS |
ISO-Week | ISO-WEEK, ISO-WK |
Month | MONTH, MONTHS, MO, MOS |
Quarter | QUARTER, QUARTERS, QTR, QTRS |
Year | YEAR, YEARS, YR, YRS |
Date Function Name | Format (Result) | Description |
|---|---|---|
DATE_TRUNC(unit,date) | date | Returns a date value truncated to the specified unit. |
DATE_PART(unit,date) | integer | Returns an integer containing the specified (unit) component of the input date. |
DATE_GMT(date) | any character data type | Converts an absolute date into the Greenwich Mean Time character equivalent with the format yyyy_mm_dd hh:mm:ss GMT. If the absolute date does not include a time, the time portion of the result is returned as 00:00:00. For example, the query: SELECT DATE_GMT('1-1-98 10:13 PM PST') returns the following value: 1998_01_01 06:13:00 GMT while the query: SELECT DATE_GMT(‘1-1-1998’) returns: 1998_01_01 00:00:00 GMT |
GMT_TIMESTAMP(s) | any character data type | Returns a twenty-three-character string giving the date s seconds after January 1, 1970 GMT. The output format is ‘yyyy_mm_dd hh:mm:ss GMT’. For example, the query: SELECT (GMT_TIMESTAMP (1234567890)) returns the following value: 2009_02_13 23:31:30 GMT while the query: (II_TIMEZONE_NAME = AUSTRALIA-QUEENSLAND) SELECT date(GMT_TIMESTAMP (1234567890)) returns: 14-feb-2009 09:31:30 |
INTERVAL (unit,date_interval) | float | Converts a date interval into a floating-point constant expressed in the unit of measurement specified by unit. The interval function assumes that there are 30.436875 days per month and 365.2425 days per year when using the mos, qtrs, and yrs specifications. For example, the query: SELECT(INTERVAL(‘days’, ‘5 years’)) returns the following value: 1826.213 |
ISDST(date) | integer | Returns 1 if date falls within Daylight Saving Time for the session timezone, else 0. |
_DATE(s) | any character data type | Returns a nine-character string giving the date s seconds after January 1, 1970 GMT. The output format is dd-mmm-yy. For example, the query: SELECT _DATE(123456) returns the following value: 2-jan-70 Note that this function formats a leading space for day values less than 10. |
_DATE4(s) | any character data type | Returns an eleven-character string giving the date s seconds after January 1, 1970 GMT. The output format is controlled by the II_DATE_FORMAT setting. For example, with II_DATE_FORMAT set to US, the query: SELECT _DATE4(123456) returns the following value: 02-jan-1970 while with II_DATE_FORMAT set to MULTINATIONAL, the query: SELECT _DATE4(123456) returns the following value: 02/01/1970 |
_TIME(s) | any character data type | Returns a five-character string giving the time s seconds after January 1, 1970 GMT, which is then adjusted for your local time zone. The output format is hh:mm (seconds are truncated). For example, the query: SELECT _TIME(123456) returns the value 02:17 for the NA-PACIFIC time zone. |
Date Column | Day of Week | Week | ISO-Week |
|---|---|---|---|
02-jan-1998 | Fri | 0 | 1 |
04-jan-1998 | Sun | 0 | 1 |
02-jan-1999 | Sat | 0 | 53 |
04-jan-1999 | Mon | 1 | 1 |
02-jan-2000 | Sun | 0 | 52 |
04-jan-2000 | Tue | 1 | 1 |
02-jan-2001 | Tue | 1 | 1 |
04-jan-2001 | Thu | 1 | 1 |
Specifier | Description |
|---|---|
%a | Abbreviated weekday name (Sun..Sat) |
%b | Abbreviated month name (Jan..Dec) |
%c | Month, numeric (0..12) |
%D | Day of the month with English suffix (1st, 2nd, 3rd, …) |
%d | Day of the month, numeric (01‑31) |
%e | Day of the month, numeric (1‑31) |
%f | Microseconds (000000..999999) |
%H | Hour (00..23) |
%h | Hour (01..12) |
%I | Hour (01..12) |
%i | Minutes, numeric (00..59) |
%j | Day of year (001..366) |
%k | Hour (0..23) |
%l | Hour (1..12) |
%M | Month name (January..December) |
%m | Month, numeric (00..12) |
%p | AM or PM |
%r | Time, 12-hour (hh:mm:ss followed by AM or PM) |
%S | Seconds (00..59) |
%s | Seconds (00..59) |
%T | Time, 24-hour (hh:mm:ss) |
%U | Week (00..53), where Sunday is the first day of the week |
%u | Week (00..53), where Monday is the first day of the week |
%V | Week (01..53), where Sunday is the first day of the week; used with %X |
%v | Week (01..53), where Monday is the first day of the week; used with %x |
%W | Weekday name (Sunday..Saturday) |
%w | Day of the week (0=Sunday..6=Saturday) |
%X | Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V |
%x | Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v |
%Y | Year, numeric (four digits) |
%y | Year, numeric (two digits) |
%% | A literal “%” character |
%x | x, for any "x" not listed above |
Col2 | Col3 | Col4 |
|---|---|---|
1526341860 | -920527466 | -1447292811 |
Aggregate Function Name | Result Data Type | Description |
|---|---|---|
ANY | integer | Returns 1 if any row in the table fulfills the where clause, or 0 if no rows fulfill the where clause. |
AVG | float, money, date (interval only) | Average (sum/count) The sum of the values must be within the range of the result data type. |
COUNT | integer | Count of non-null occurrences |
MAX | same as argument | Maximum value |
MIN | same as argument | Minimum value |
SUM | integer, float, money, date (interval only) | Column total |
STDDEV_POP | float | Compute the population form of the standard deviation (square root of the population variance of the group). |
STDDEV_SAMP | float | Computes the sample form of the standard deviation (square root of the sample variance of the group). |
VAR_POP | float | Computes the population form of the variance (sum of the squares of the difference of each argument value in the group from the mean of the values, divided by the count of the values). |
VAR_SAMP | float | Computes the sample form of the variance (sum of the squares of the difference of each argument value in the group from the mean of the values, divided by the count of the values minus 1). |
Binary Aggregate Function Name | Result Data Type | Description |
|---|---|---|
REGR_COUNT (indep_parm, dep_parm) | integer | Count of rows with non-null values for both dependent and independent variables. |
COVAR_POP (indep_parm, dep_parm) | float | Population covariance (sum of the products of the difference of the independent variable from its mean, times the difference of the dependent variable from its mean, divided by the number of rows). |
COVAR_SAMP (indep_parm, dep_parm) | float | Sample covariance (sum of the products of the difference of the independent variable from its mean, times the difference of the dependent variable from its mean, divided by the number of rows minus 1). |
CORR (indep_parm, dep_parm) | float | Correlation coefficient (ratio of the population covariance divided by the product of the population standard deviation of the independent variable and the population standard deviation of the dependent variable). |
REGR_R2 (indep_parm, dep_parm) | float | Square of the correlation coefficient. |
REGR_SLOPE (indep_parm, dep_parm) | float | Slope of the least-squares-fit linear equation determined by the (independent variable, dependent variable) pairs. |
REGR_INTERCEPT (indep_parm, dep_parm) | float | Y-intercept of the least-squares-fit linear equation determined by the (independent variable, dependent variable) pairs. |
REGR_SXX (indep_parm, dep_parm) | float | Sum of the squares of the independent variable. |
REGR_SXY (indep_parm, dep_parm) | float | Sum of the product of the independent variable and the dependent variable. |
REGR_SYY (indep_parm, dep_parm) | float | Sum of the squares of the dependent variable. |
REGR_AVGX (indep_parm, dep_parm) | float | Average of the independent variables. |
REGR_AVGY (indep_parm, dep_parm) | float | Average of the dependent variables. |
Name | Exemptions |
|---|---|
Smith | 0 |
Jones | 2 |
Tanghetti | 4 |
Fong | Null |
Stevens | Null |
Function Name | Precision of Result | Scale of Result |
|---|---|---|
COUNT | Not applicable | Not applicable |
SUM | 39 | Same as argument |
AVG | 39 | Scale of argument + 1 (to a maximum of 39) |
MAX | Same as argument | Same as argument |
MIN | Same as argument | Same as argument |