Literals

Literals represent constants that 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 than the assigned or comparison type, the data type that is smaller is implicitly converted.

You can group literals as follows:

• Numeric literals
• Boolean literals
• Date and time literals
• Interval literals
• String literals
• NULL literals

Examples

Literal	Description
123	Integer	See Numeric literals
-123.456	Decimal number
1.23456E-32	Double value
TRUE	Boolean value	See Boolean literals
DATE '2007-03-31'	Date	See Date and time literals
TIMESTAMP '2007-03-31 12:59:30.123'	Timestamp
INTERVAL '13-03' YEAR TO MONTH	Interval (YEAR TO MONTH )	See Interval literals
INTERVAL '1 12:00:30.123' DAY TO SECOND	Interval (DAY TO SECOND)
'ABC'	String value	See String literals
NULL	NULL (no value)	See NULL literals

The following formats are specified for string:

Literal	Format	Description
DATE	'YYYY-MM-DD'	For example: DATE '2024-01-23'
TIMESTAMP	'YYYY-MM-DD HH24:MI:SS.FF9'	This literal has the data type TIMESTAMP(p), where the fractional seconds precision p corresponds to the number of fractional digits given in the literal. Examples: TIMESTAMP '2024-01-01 00:00:00.123' has the data type TIMESTAMP(3) (millisecond precision).

• The integers int define the number of years or months in the interval. For YEAR TO MONTH the first integer is the number of years, the second one the number of months which must be in the range from 0 to 11.

• precision specifies the maximal number of digits (1-9). If this parameter is not set, 2 digits are allowed.

Examples

Interval literals	Meaning
INTERVAL '5' MONTH	5 months
INTERVAL '130' MONTH(3)	130 months
INTERVAL '27' YEAR	27 years
INTERVAL '2-1' YEAR TO MONTH	2 years and 1 month
INTERVAL '100-1' YEAR(3) TO MONTH	100 years and 1 month

interval_day_to_second_literal::=

 

 

• 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 example table below. 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, 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 literals	Meaning
INTERVAL '5' DAY	5 days
INTERVAL '100' HOUR(3)	100 hours
INTERVAL '6' MINUTE	6 minutes
INTERVAL '1.99999' SECOND(2,2)	2.00 seconds (rounded after the second place)
INTERVAL '10:20' HOUR TO MINUTE	10 hours and 20 minutes
INTERVAL '2 23:10:59' DAY TO SECOND	2 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

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