Date, time and timestamp literals in DB2

Datetime constants overview

A datetime constant is a character string constant written in a format Db2 recognizes as a date, time, or timestamp. Internally Db2 stores datetime types in a special format; on input and output it converts to and from string representations.

You can also use ANSI/ISO-style typed constants that start with the keywords DATE, TIME, or TIMESTAMP followed by a string constant. Those forms make the intended data type obvious to readers and to the SQL parser.

Prefer real datetime types over storing dates as CHAR or INTEGER. Literals then compare cleanly, date arithmetic works, and invalid calendar values are rejected instead of silently stored as junk strings.

Date literals

A DATE is a three-part value: year, month, and day, in the range 0001-01-01 through 9999-12-31. Date literals are strings that follow a valid date representation.

1
2
3
4
SELECT *
  FROM HR.EMPLOYEE
 WHERE HIREDATE >= DATE '2020-01-01'
   AND HIREDATE <  '2021-01-01';

Rules for the ANSI DATE string-constant form include:

• No leading blanks
• Year needs leading zeros (four-digit year)
• Month and day may omit leading zeros (0 is assumed for omitted digits)
• Trailing blanks may be included

Sites also accept other string styles (USA, EUR, JIS, and related) depending on product rules. For application SQL that travels between environments, ISO-like yyyy-mm-dd is the least ambiguous choice. Display format for SELECT results can still follow installation defaults or CHAR(... format) overrides even when you entered ISO input.

Common mistakes with dates

• Writing two-digit years and hoping Db2 guesses the century
• Comparing a DATE column to a string that is not a valid date representation
• Using a slash format that is valid at one site and rejected at another—standardize
• Confusing DATE '2024-03-15' with the character string that merely looks similar when assigned to a CHAR column

Time literals

A TIME is a three-part value for hour, minute, and second (range from 00.00.00 through 24.00.00 in Db2’s documented limits). Time literals are valid time strings, optionally written as TIME '...'.

1
2
3
VALUES TIME '14:30:00';
VALUES TIME '2:30 PM';
VALUES '14.30.00';

Useful rules:

• Leading blanks are not allowed; trailing blanks may appear
• Leading zeros on the hour may be omitted; seconds may be omitted (zeros assumed)
• USA format uses AM/PM with a blank before the meridian; hour is 1–12 (with documented midnight/noon mappings)
• 12:00 AM maps to midnight; 12:00 PM maps to noon—confirm mappings when converting USA strings
• When minutes and seconds are zero, hour 24 may be allowed in non-USA forms for “end of day” style values

Time-only columns are less common than timestamps in modern designs, but they still appear for schedules (“store opens at…”). Be explicit with 24-hour ISO-style times in interfaces that mix locales.

Timestamp literals

A TIMESTAMP combines date and time, optionally with fractional seconds and optionally with a time zone. Fractional second digits can range from 0 to 12; that count becomes precision p for TIMESTAMP(p).

1
2
3
VALUES TIMESTAMP '2024-03-15 14:30:00';
VALUES TIMESTAMP '2024-03-15-14.30.00.123456';
VALUES '2024-03-15T14:30:00.123456789';

Separators and shapes

Documented timestamp-without-time-zone patterns include combinations of:

• Blank or minus or T between date and time portions
• Colons or periods separating hour, minute, second
• Optional fractional seconds after a decimal point

Examples of accepted shapes (see SQL Reference tables for the full set):

1
2
3
4
5
-- Illustrative shapes
'yyyy-mm-dd hh:mm:ss'
'yyyy-mm-dd hh.mm.ss.nnnnnn'
'yyyy-mm-dd-hh.mm.ss'
'yyyy-mm-ddThh:mm:ss.nnnnnnnnnnnn'

Leading blanks are disallowed; trailing blanks may appear. Leading zeros may be omitted from month, day, and hour (and time zone hour); minute and second elements need their leading zeros. Hour 24 is allowed only when minutes, seconds, and fractions are zero.

Timestamps with time zones

For TIMESTAMP WITH TIME ZONE, the string includes an offset or UTC designator, for example:

• ±th:tm — hours and minutes offset (−24:00 through +24:00)
• ±th — hours only (minutes assumed 00)
• Z — UTC

1
2
VALUES TIMESTAMP '2024-03-15 14:30:00+02:00';
VALUES TIMESTAMP '2024-03-15 14:30:00Z';

Without a zone, you get TIMESTAMP WITHOUT TIME ZONE semantics. Mixing zoned and unzoned data in one expression requires care—explicit CAST and clear application rules beat guesswork.

Literals versus special registers

1
2
3
4
5
6
INSERT INTO APP.AUDIT_LOG (EVENT_TS, NOTE)
VALUES (CURRENT TIMESTAMP, 'row inserted');
 
SELECT *
  FROM APP.ORDERS
 WHERE ORDER_DATE = DATE '2024-01-31';

CURRENT DATE, CURRENT TIME, and CURRENT TIMESTAMP evaluate when the statement runs. Literals are fixed values in the statement text. Use registers for “now,” literals for historical or planned fixed points. CURRENT TIME ZONE is related but answers a different question (session time zone displacement).

Practical tips

• Prefer typed DATE/TIME/TIMESTAMP constants in teaching examples—they document intent
• Standardize on ISO-like strings in application code paths
• Match column precision — inserting a 9-digit fraction into TIMESTAMP(6) follows assignment rules (truncation/rounding per product rules)
• Do not store timestamps as CHAR unless you are forced by a legacy interface—and then validate aggressively
• Test USA AM/PM conversions if any feed still sends 12-hour strings

Explain It Like I'm Five

A date literal is like writing a birthday on a party invitation: year, month, and day. A time literal is like writing “the party starts at three o’clock.” A timestamp is writing both—“March 15, 2024 at 3:00”—and sometimes how many tiny ticks after the second, and which town’s clock (the time zone). CURRENT TIMESTAMP is looking at the clock on the wall when you mail the invitation. A literal is writing a fixed time that does not change when you look at the wall later.

Exercises

1. Write typed DATE, TIME, and TIMESTAMP literals for 31 December 2025 at 23:59:59 with six fractional zeros.
2. Convert 7:05 PM USA into a 24-hour time literal.
3. Explain when you would use CURRENT DATE instead of DATE '2024-01-01' in a WHERE clause.
4. Add a +01:00 time zone to a timestamp literal and state whether the type is WITH or WITHOUT TIME ZONE.
5. List two separator styles between date and time portions that Db2 documents for timestamps.