Literals

Literals represent constants, which possess a specific value as well as a corresponding data type. Literals can be entered into tables as values or used within queries as constants, for example, in comparisons or function parameters. If the literal displays a different data type to the assigned or comparison type, the data type which is smaller is converted implicitly.

You can group Literals as follows:

There is also a special literal for the NULL value.

Example

123

Integer number (integral decimal number)

-123.456

Decimal number

1.23456E-32

Double value

TRUE

Boolean value

DATE '2007-03-31'

Date

TIMESTAMP '2007-03-31 12:59:30.123'

Timestamp

INTERVAL '13-03' YEAR TO MONTH

Interval (YEAR TO MONTH )

INTERVAL '1 12:00:30.123' DAY TO SECOND

Interval (DAY TO SECOND)

'ABC'

String

NULL

NULL value

Numeric Literals

Numeric literals represent a number. If the number is represented exactly in a DECIMAL type, Exasol chooses the smallest literal type. Otherwise the type of the literal is DOUBLE.

Numeric literals can be distinguished as follows:

integer_literal::=

integer literal

decimal_literal::=

double_literal::=

Boolean Literals

There are only three valid values for Boolean literals: TRUE, FALSE, and UNKNOWN (which is similar to NULL). Instead of a literal, you can also use strings (for example, 'T' or 'True') or numbers (0 and 1). For more details, refer to Boolean Data Type section.

boolean_literal::=

Date/Time Literals

Date/Time literals represent a particular point in time. They have a fixed format, and therefore, they are not as flexible as the TO_DATE or TO_TIMESTAMP conversion functions. In most cases, these functions should be given priority over literals. For more information refer to Built-in functions and Data/Time format models sections.

date_literal::=

timestamp_literal::=

The following formats are specified for string:

Type Format Explanation
DATE 'YYYY-MM-DD' Fixed format for DATE literals ('Year-Month-Day')
TIMESTAMP 'YYYY-MM-DD HH:MI:SS.FF3' Fixed format for TIMESTAMP literals ('Year-Month- Day Hours-Minutes-Seconds. Milliseconds')

Interval Literals

Interval literals describe an interval of time and are very useful for datetime arithmetic.

interval_year_to_month_literal::=

Where:

  • int[-int] defines integers values for the number of years and months. In case of YEAR TO MONTH you have to specify int-int
  • The optional parameter precision (1-9) specifies the maximal number of digits. Without this parameter two digits are allowed (months from 0 to 11)
Example
INTERVAL '5' MONTH Five months
INTERVAL '130' MONTH (3) 130 months
INTERVAL '27' YEAR 27 years
INTERVAL '2-1' YEAR TO MONTH Two years and one month
INTERVAL '100-1' YEAR(3) TO MONTH 100 years and one month

interval_day_to_second_literal::=

 

 

Where:

  • int defines the number of days (see Precision for maximum number of digits)
  • time_expr specifies a time value in format HH[:MI[:SS[.n]]] or MI[:SS[.n]] or SS[.n]. Valid values are 0-23 for hours (HH), 0-59 for minutes (MI) and 0-59.999 for seconds (SS). The parameter precision defines the maximal number of digits for the leading field which allows you also to use larger numbers for hours, minutes and seconds as shown in the below example table . The parameter fractional_precision defines at which position the fractional part of the seconds are rounded (0-9, default is 3). In time_expr, you have to specify the whole used range, for example, HH:MI in case of HOUR TO MINUTE.
  • The precision of seconds is limited to 3 decimal places (similar to timestamp values), although you can specify more digits in the literal
  • The optional parameter precision(1-9) specifies the maximum number of digits for the leading field. By default, two digits are allowed
  • The interval borders must be descending. For example, SECOND TO MINUTE is not valid.
Example
INTERVAL '5' DAY Five days
INTERVAL '100' HOUR(3) 100 hours
INTERVAL '6' MINUTE Six minutes
INTERVAL '1.99999' SECOND(2,2) 2.00 seconds (rounded after the second place)
INTERVAL '10:20' HOUR TO MINUTE Ten hours and 20 minutes
INTERVAL '2 23:10:59' DAY TO SECOND Two days, 23 hours, 10 minutes and 59 seconds
INTERVAL '23:10:59.123' HOUR(2) TO SECOND(3) 23 hours, 10 minutes and 59.123 seconds

String Literals

String literals are used to represent text. The maximum length of a string is limited by the CHAR data type (see String Data Types for more information). String literals use the smallest character sets available. ASCII character set will be used if only ASCII characters are included, else the UTF-8 character set will be used.

string_literal::=

Note: An empty string literal ('') is evaluated as NULL value.

To use single quotes within a string, two single quotes are written next to one another. For example, the literal 'AB''C' represents the value AB'C.

NULL Literals

The NULL literal is used in SQL to indicate a value as being "not known".

null_literal::=