Db2 DATE data type

Internal format and range

A DATE is a three-part value—year, month, and day—designating a calendar day using the Gregorian calendar model Db2 assumes from year 1 onward. Years run from 0001 to 9999. Months are 1–12. Days are 1–28/29/30/31 depending on the month and leap year rules.

Internally, Db2 stores a date as a 4-byte string of packed decimal digits: two bytes for the year, one for the month, one for the day. That compact form is not what COBOL usually holds in Working-Storage. When you SELECT a DATE into a host variable, Db2 converts to an external string—commonly 10 bytes (for example YYYY-MM-DD style) unless a local date exit extends formats up to much longer strings.

1
2
3
4
5
6
CREATE TABLE HR.EMPLOYEE (
    EMPNO      CHAR(6) NOT NULL,
    HIREDATE   DATE NOT NULL,
    BIRTHDATE  DATE,
    PRIMARY KEY (EMPNO)
);

Date literals

You do not type a “binary DATE constant” in SQL the way you type an integer. Date constants are character strings in recognized formats, or ANSI-style typed constants:

1
2
3
4
5
6
INSERT INTO HR.EMPLOYEE (EMPNO, HIREDATE, BIRTHDATE)
VALUES ('000150', DATE '2005-05-01', '1980-12-15');
 
SELECT EMPNO, HIREDATE
  FROM HR.EMPLOYEE
 WHERE HIREDATE >= DATE '2000-01-01';

Exact accepted string formats (ISO, USA, EUR, JIS, local) depend on your subsystem and application defaults. Prefer unambiguous ISO-style DATE 'YYYY-MM-DD' forms in portable SQL. The dedicated literals page walks format tables in more depth.

CURRENT DATE

CURRENT DATE is a special register: each time the statement runs, Db2 supplies the current date. Use it for “as of today” defaults and comparisons—not as a substitute for auditing a business event time (that often needs TIMESTAMP).

1
2
3
4
5
6
7
SELECT EMPNO
  FROM HR.EMPLOYEE
 WHERE HIREDATE = CURRENT DATE;
 
UPDATE HR.EMPLOYEE
   SET HIREDATE = CURRENT DATE
 WHERE EMPNO = '000150';

Common date functions (intro)

1
2
3
4
5
6
7
8
9
10
SELECT EMPNO,
       YEAR(HIREDATE)  AS HIRE_YEAR,
       MONTH(HIREDATE) AS HIRE_MONTH,
       DAY(HIREDATE)   AS HIRE_DAY,
       CHAR(HIREDATE)  AS HIRE_CHAR
  FROM HR.EMPLOYEE;
 
-- Labeled duration arithmetic (add one month):
SELECT EMPNO, HIREDATE + 1 MONTH AS PROBATION_END
  FROM HR.EMPLOYEE;

Date arithmetic with durations is powerful and slightly subtle around month-ends (what is January 31 plus one month?). Read IBM rules when building billing cycles. For formatting beyond CHAR’s defaults, shops also use exits or formatting functions depending on version and standards.

Host-variable and string conversion gotchas

• Format mismatch — program sends MM/DD/YYYY while Db2 expects another style → SQLCODE conversion errors
• Length mismatch — local date exit expects longer than 10 bytes; DCLGEN length must match install options
• CHAR vs DATE columns — storing dates in CHAR(10) skips calendar validation and complicates arithmetic
• NULL indicators — nullable BIRTHDATE needs a null indicator on FETCH
• Comparing to TIMESTAMP — know promotion rules; prefer explicit DATE() or cast when mixing types

A robust pattern: keep columns as DATE, move values through well-known string formats at the edges, and centralize formatting in SQL functions or a thin conversion layer—not scattered PIC clauses with conflicting assumptions.

1
2
3
4
-- Prefer typed dates in predicates when possible:
WHERE HIREDATE BETWEEN DATE '2020-01-01' AND DATE '2020-12-31'
 
-- Rather than hoping a CHAR column sorts and validates as a calendar

Explain It Like I'm Five

A DATE is a sticker with only the day on the calendar—year, month, and day—like “birthday” or “first day of school.” It does not say what time the party starts (that is TIME or TIMESTAMP). Db2 keeps a tiny packed code inside, but when it talks to your program it usually writes the date as readable text such as 2024-03-15.

Exercises

1. State the DATE range and internal byte length from memory.
2. Write an INSERT using DATE '...' and a SELECT using CURRENT DATE.
3. List YEAR, MONTH, DAY results you would expect for DATE '2024-02-29'.
4. Describe two host-variable mistakes that cause date conversion SQLCODEs.
5. Why is DATE better than CHAR(8) for “YYYYMMDD” business dates in most new designs?