DATE versus TIMESTAMP in IBM i SQL

A quick question that crops up regularly in the IBM i trenches is the difference between DATE and TIMESTAMP in SQL. (You wrote “datestamp”, but in the IBM i world, we work with DATE and TIMESTAMP.)

Here is the clear, practical version.

DATE

A DATE value holds only the calendar day: year, month and day.

• Range: 0001-01-01 to 9999-12-31
• Internal storage: 4 bytes
• Typical display: YYYY-MM-DD (ISO format)
• Example: 2026-08-06

Use DATE when the time of day does not matter. Birth dates, invoice dates, due dates, holiday calendars – all classic DATE territory.

TIMESTAMP

A TIMESTAMP value holds the full point in time: year, month, day, hour, minute, second, and optional fractions of a second.

• You can specify precision: TIMESTAMP(0) up to TIMESTAMP(12). Default is TIMESTAMP(6) (microseconds).
• Internal storage: 7 to 13 bytes depending on the fractional precision
• Typical display: YYYY-MM-DD-HH.MM.SS.nnnnnn
• Example: 2026-08-06-14.30.15.123456

Use TIMESTAMP when you need both the date and the exact time (or even fractions of a second). Audit logs, transaction times, last-changed stamps, and any “when exactly did this happen” requirement belong here.

Key practical differences

1. ContentDATE = day only TIMESTAMP = day + time of day (plus fractions if you ask for them)
2. Assignment behaviour
   • Put a TIMESTAMP into a DATE column and the time portion is simply dropped.
   • Put a DATE into a TIMESTAMP column and the time is set to midnight (00.00.00).
3. Special registers
   • CURRENT DATE gives you today’s date
   • CURRENT TIMESTAMP gives you the precise current date and time (including fractions)
4. Storage and indexingDATE is smaller and often sufficient. TIMESTAMP takes a little more space but gives you a single, unambiguous point in time. Many experienced developers prefer one TIMESTAMP column over separate DATE and TIME columns when the values belong together.

Quick rule of thumb

• Need only the day? → DATE
• Need the exact moment? → TIMESTAMP (usually TIMESTAMP(0) or TIMESTAMP(6) is fine)

Simple examples

SQL

-- Create a table
CREATE TABLE ORDERS (
 ORDER_ID INTEGER,
 ORDER_DATE DATE, -- just the day
 ORDER_TS TIMESTAMP(6) -- exact moment
);

-- Insert
INSERT INTO ORDERS VALUES
(1, CURRENT DATE, CURRENT TIMESTAMP);

-- Compare
SELECT * FROM ORDERS
WHERE ORDER_DATE = '2026-08-06';

SELECT * FROM ORDERS
WHERE ORDER_TS >= '2026-08-06-00.00.00'
 AND ORDER_TS < '2026-08-07-00.00.00';

That is the whole story in plain English. DATE for the calendar day, TIMESTAMP when the clock matters too.

If you want to see conversion functions, how to extract parts with YEAR/MONTH/DAY/HOUR, or the difference between TIMESTAMP(0) and TIMESTAMP(6) in real code, just shout and we will dig deeper.