4.7 Literals

Literals are a type of expression that specify a constant value (they are also called constants). You can specify literals wherever SQL syntax allows expressions. Some SQL constructs allow literals but prohibit other forms of expressions.

There are three types of literals:

• Numeric
• Character string
• Date-time

The following sections discuss each type of literal.

4.7.1 Numeric Literals

A numeric literal is a string of digits that SQL interprets as a decimal number. SQL allows the string to be in a variety of formats, including scientific notation.

Syntax

[+|-]{[0-9][0-9]...}[.[0-9][0-9]...][[E|e][+|-][0-9]{[0-9]}]

Examples

The following are all valid numeric strings:

123
123.456
-123.456
12.34E-04

4.7.2 Character String Literals

A character string literal is a string of characters enclosed in single quotation marks ( ' ).

To include a single quotation mark in a character-string literal, precede it with an additional single quotation mark. The following SQL examples show embedding quotation marks in character-string literals:

insert into quote values('unquoted literal');
insert into quote values('''single-quoted literal''');
insert into quote values('"double-quoted literal"');
insert into quote values('O''Hare');
select * from quote;

c1
unquoted literal
'single-quoted literal'
"double-quoted literal"
O'Hare

To insert a character-string literal that spans multiple lines, enclose each line in single quotation marks. The following SQL examples shows this syntax, as well as embedding quotation marks in one of the lines:

insert into quote2 values ('Here''s a very long character string '
    'literal that will not fit on a single line.');
1 record inserted.
select * from quote2;
C1
--
Here's a very long character string literal that will not fit on a single
line.

4.7.3 Date-Time Literals

SQL supports special formats for literals to be used in conjunction with date-time data types. Basic predicates and the VALUES clause of INSERT statements can specify date literals directly for comparison and insertion into tables. In other cases, you need to convert date literals to the appropriate date-time data type with the CAST, CONVERT, or TO_DATE scalar functions.

Enclose date-time literals in single quotation marks.

4.7.3.1 Date Literals

Date literals specify a day, month, and year. By default, SQL supports any of the following formats, enclosed in single quotation marks ( ' ). Check with your administrator to see if the set of supported formats has been changed by setting the TPE_DFLT_DATE runtime variable.

Syntax

date-literal ::
    {d 'yyyy-mm-dd'}
|   mm-dd-yyyy
|   mm/dd/yyyy
|   yyyy-mm-dd
|   yyyy/mm/dd
|   dd-mon-yyyy
|   dd/mon/yyyy

Arguments

{d 'yyyy-mm-dd'}

A date literal enclosed in an escape clause compatible with ODBC. Precede the literal string with an open brace ( { ) and a lowercase d. End the literal with a close brace. For example:

INSERT INTO DTEST VALUES ({d '1994-05-07'})

If you use the ODBC escape clause, you must specify the date using the format yyyy-mm-dd.

dd

The day of month as a 1- or 2-digit number (in the range 01-31).

mm

The month value as a 1- or 2-digit number (in the range 01-12).

mon

The first 3 characters of the name of the month (in the range 'JAN' to 'DEC').

yyyy

The year as 4-digit number. By default, SQL generates an Invalid date string error if the year is specified as anything but 4 digits. Check with your administrator to see if this default behavior has been changed by setting the DH_Y2K_CUTOFF runtime variable.

Examples

The following SQL examples show some of the supported formats for date literals:

CREATE TABLE T2 (C1 DATE, C2 TIME);
INSERT INTO T2 (C1) VALUES('5/7/56');
INSERT INTO T2 (C1) VALUES('7/MAY/1956');
INSERT INTO T2 (C1) VALUES('1956/05/07');
INSERT INTO T2 (C1) VALUES({d '1956-05-07'});
INSERT INTO T2 (C1) VALUES('29-sEP-1952');
SELECT C1 FROM T2;

c1
1956-05-07
1956-05-07
1956-05-07
1956-05-07
1952-09-29

4.7.3.2 Time Literals

Time literals specify an hour, minute, second, and millisecond, using the following format, enclosed in single quotation marks ( ' ):

Syntax

time-literal ::
    {t 'hh:mi:ss'}
|   hh:mi:ss[:mls]

Arguments

{t 'hh:mi:ss'}

A time literal enclosed in an escape clause compatible with ODBC. Precede the literal string with an open brace ( { ) and a lowercase t. End the literal with a close brace. For example:

INSERT INTO TTEST VALUES ({t '23:22:12'})

If you use the ODBC escape clause, you must specify the time using the format hh:mi:ss.

hh

The hour value as a 1- or 2-digit number (in the range 00 to 23).

mi

The minute value as a 1- or 2-digit number (in the range 00 to 59).

ss

The seconds value as a 1- or 2-digit number (in the range 00 to 59).

mls

The milliseconds value as a 1- to 3-digit number (in the range 000 to 999).

Examples

The following SQL examples show some of the formats SQL will and will not accept for time literals:

INSERT INTO T2 (C2) VALUES('3');
error(-20234): Invalid time string
INSERT INTO T2 (C2) VALUES('8:30');
error(-20234): Invalid time string
INSERT INTO T2 (C2) VALUES('8:30:1');
INSERT INTO T2 (C2) VALUES('8:30:');
error(-20234): Invalid time string
INSERT INTO T2 (C2) VALUES('8:30:00');
INSERT INTO T2 (C2) VALUES('8:30:1:1');
INSERT INTO T2 (C2) VALUES({t'8:30:1:1'});

SELECT C2 FROM T2;

c2
08:30:01
08:30:00
08:30:01
08:30:01

4.7.3.3 Timestamp Literals

Timestamp literals specify a date and a time separated by a space, enclosed in single quotation marks ( ' ):

Syntax

    {ts 'yyyy-mm-dd hh:mi:ss'}
|   ' date-literal time-literal '

Arguments

{ts 'yyyy-mm-dd hh:mi:ss'}

A timestamp literal enclosed in an escape clause compatible with ODBC. Precede the literal string with an open brace ( { ) and a lowercase ts. End the literal with a close brace. For example:

INSERT INTO DTEST
VALUES ({ts '1956-05-07 10:41:37'})

If you use the ODBC escape clause, you must specify the timestamp using the format yyyy-mm-dd hh:mi:ss.

date-literal

A date literal.

time-literal

A time literal.

Example

SELECT * FROM DTEST WHERE C1 = {ts '1956-05-07 10:41:37'}