Constants

A constant is a literal value.

SQL syntax

{IntegerValue | FloatValue |FloatingPointLiteral|
    FixedPointValue | 'CharacterString'|
    'NationalCharacterString' | HexadecimalLiteral |
    'DateString' | DateLiteral |'TimeString' | 
    TimeLiteral | 'TimestampString' | TimestampLiteral |
    IntervalLiteral | BINARY_FLOAT_INFINITY |
    BINARY_DOUBLE_INFINITY | -BINARY_FLOAT_INFINITY |
    -BINARY_DOUBLE_INFINITY | BINARY_FLOAT_NAN |
     BINARY_DOUBLE_NAN
}

Constant	Description
`IntegerValue`	A whole number compatible with `TT_INTEGER`, `TT_BIGINT` or `TT_SMALLINT` data types or an unsigned whole number compatible with the `TT_TINYINT` data type For example: 155, 5, -17
`FloatValue`	A floating-point number compatible with the `BINARY_FLOAT` or `BINARY_DOUBLE` data types Examples: .2E-4, 1.23e -4, 27.03, -13.1
`FloatingPointLiteral`	Floating point literals These are compatible with the `BINARY_FLOAT` and `BINARY_DOUBLE` data types. `f` or `F` indicates that the number is a 32-bit floating point number (of type `BINARY_FLOAT`). `d` or `D` indicates that the number is a 64-bit floating point number (of type `BINARY_DOUBLE`). For example: 123.23F, 0.5d
`FixedPointValue`	A fixed-point number compatible with the `BINARY_FLOAT`, `BINARY_DOUBLE` or `NUMBER` data types For example: 27.03
`CharacterString`	A character string compatible with `CHAR` or `VARCHAR2` data types String constants are delimited by single quotation marks. For example: 'DON''T JUMP!' Two single quotation marks in a row are interpreted as a single quotation mark, not as string delimiters or the empty string.
`NationalCharacterString`	A character string compatible with `NCHAR` or `NVARCHAR2` data types National string constants are preceded by an indicator consisting of either `N` or `n`, and delimited by single quotation marks. For example: N'Here''s how!' Two single quotation marks in a row are interpreted as a single quotation mark. The contents of a national string constant may consist of any combination of: ASCII characters UTF-8 encoded Unicode characters Escaped Unicode characters ASCII characters and UTF-8 encoded characters are converted internally to their corresponding UTF-16 format Unicode equivalents. Escaped Unicode characters are of the form `\uxxxx`, where `xxxx` is the four hexadecimal-digit representation of the Unicode character. For example: N'This is an \u0061' Is equivalent to: N'This is an a' The `\u` itself can be escaped with another `\`. The sequence `\\u` is always converted to `\u`. No other escapes are recognized.
`HexadecimalLiteral`	Hexadecimal literals Hexadecimal literals containing digits 0 - 9 and A - F (or a - f) are compatible with the `BINARY`, `VARBINARY`, `CHAR`, `VARCHAR2` and `BLOB` data types. A `HexadecimalLiteral` constant should be prefixed with the characters "`0x`." For example: 0xFFFAB0880088343330FFAA7 Or: 0x000A001231 Hexadecimal digits provided with an odd length are pre-fixed with a zero to make it even. For example, the value `0x123` is converted to `0x0123`. If you provide a character literal, the binary values of the characters are used. For example, the following demonstrates what is stored when inserting a hexadecimal literal and a character literal in a `VARBINARY` column `colbin` in table `tabvb`: Command> INSERT INTO tabvb VALUES (0x1234); 1 row inserted. Command> INSERT INTO tabvb VALUES ('1234'); 1 row inserted. Command> SELECT colbin FROM tabvb; < 1234 > < 31323334 > 2 rows found. However, Oracle Database differs in that it only accepts character literals, such as '1234', and translates the character literal as a binary literal of 0x1234. As a result, `insert into tabvb values ('1234');` behaves differently between Oracle Database and TimesTen. Oracle Database does not accept 0x1234 as a hexadecimal literal.
`DateString`	A string of the format `YYYY-MM-DD` `HH:MI:SS` enclosed in single quotation marks (`'`) For example: '2007-01-27 12:00:00' The `YYYY` field must have a four-digit value. The `MM` and `DD` fields must have two-digit values. The only spaces allowed are trailing spaces (after the day field). The range is from `'-4713-01-01'` (January 1, 4712 BC) to `'9999-12-31'`, (December 31, 9999). The time component is not required. For example: '2007-01-27' For `TT_DATE` data types, the string is of format `YYYY-MM-DD` and ranges from `'1753-01-01'` to `'9999-12-31'`.
`DateLiteral`	Format: `DATE` `DateString` For example: DATE '2007-01-27' or DATE '2007-01-27 12:00:00' For `TT_DATE` data types, use the literal `TT_DATE`. For example: TT_DATE '2007-01-27'. Do not specify a time portion with the `TT_DATE` literal. The `DATE` keyword is case-insensitive. TimesTen also supports ODBC date-literal syntax. For example: {d '2007-01-27'}. See ODBC documentation for details.
`TimeString`	A string of the format `HH:MI:SS` enclosed in single quotation marks (`'`) For example: '20:25:30' The range is `'00:00:00'` to `'23:59:59'`, inclusive. Every component must be two digits. The only spaces allowed are trailing spaces (after the seconds field).
`TimeLiteral`	Format: `TIME` `TimeString` For example: TIME '20:25:30' The `TIME` keyword is case-insensitive. Usage examples: INSERT INTO timetable VALUES (TIME '10:00:00'); SELECT * FROM timetable WHERE col1 < TIME '10:00:00'; TimesTen also supports ODBC time literal syntax. For example: {t '12:00:00'}
`TimestampString`	A string of the format `YYYY-MM-DD HH:MI:SS [.FFFFFFFFF]` -enclosed in single quotation marks (') The range is from `'-4713-01-01'` (January 1, 4712 BC) to `'9999-12-31'` (December 31, 9999). The year field must be a four-digit value. All other fields except for the fractional part must be two-digit values. The fractional field can consist of zero to nine digits. For `TT_TIMESTAMP` data types, a string of format `YYYY-MM-DD HH:MI:SS[.FFFFFF]` enclosed in single quotation marks('). The range is from '`1753-01-01 00:00:00.000000'` to `'9999-12-31 23:59:59.999999'`. The fractional field can consist of zero to six digits. If you have a `CHAR` column called `C1`, and want to enforce the `TIME` comparison, you can do the following: SELECT * FROM testable WHERE C1 = TIME '12:00:00' In this example, each `CHAR` value from `C1` is converted into a `TIME` value before comparison, provided that values in `C1` conform to the proper `TIME` syntax.
`TimestampLiteral`	Format: `TIMESTAMP` `TimestampString` For example: TIMESTAMP '2007-01-27 11:00:00.000000' For `TIMESTAMP` data types, the fraction field supports from zero to nine digits of fractional seconds. For `TT_TIMESTAMP` data types, the fraction field supports from zero to six digits of fractional seconds. The `TIMESTAMP` keyword is case-insensitive. Use literal syntax to enforce `DATE/TIME/TIMESTAMP` comparisons for `CHAR` and `VARCHAR2` data types. TimesTen also supports ODBC timestamp literal syntax. For example: {ts '9999-12-31 12:00:00'}
`IntervalLiteral`	Format: `INTERVAL [+\-]` `CharacterString` `IntervalQualifier` For example: INTERVAL '8' DAY
`BINARY_FLOAT_INFINITY \| BINARY_DOUBLE_INFINITY`	Positive infinity `INF` (positive infinity) is an IEEE floating-point value that is compatible with the `BINARY_FLOAT` and `BINARY_DOUBLE` data types. Use the constant values `BINARY_FLOAT_INFINITY` or `BINARY_DOUBLE_INFINITY` to represent positive infinity.
`-BINARY_FLOAT_INFINITY \| -BINARY_DOUBLE_INFINITY`	Negative infinity `-INF` (negative infinity) is an IEEE floating-point value that is compatible with the `BINARY_FLOAT` and `BINARY_DOUBLE` data types. Use the constant values -`BINARY_FLOAT_INFINITY` and -`BINARY_DOUBLE_INFINITY` to represent negative infinity.
`BINARY_FLOAT_NAN` \| `BINARY_DOUBLE_NAN`	Non-numbers `NaN` ("not a number") is an IEEE floating-point value that is compatible with the `BINARY_FLOAT` and `BINARY_DOUBLE` data types. Use the constant values `BINARY_FLOAT_NAN` or `BINARY_DOUBLE_NAN` to represent `NaN`.