Conversion and datetime helper functions in DB2

Conversion helpers versus CAST

Functional conversions such as SMALLINT(expr) and REAL(expr) live in schema SYSIBM. They are the same idea as CAST(expr AS SMALLINT) and CAST(expr AS REAL): take a value, produce another built-in type, error or null according to the conversion rules. CAST is the SQL-standard spelling and scales to every target type (DECIMAL with precision, DATE, XMLCAST for XML). Use CAST in new portable SQL. You will still read SMALLINT() and REAL() in decades of COBOL/SQL.

INTEGER, BIGINT, DECIMAL, DOUBLE, DECFLOAT, CHAR, VARCHAR, DATE, TIME, TIMESTAMP, and XMLCAST are covered on the conversion-and-casting page. Here we zoom in on the two remaining numeric helpers the checklist groups with calendar extractors.

SMALLINT

SMALLINT returns a small integer (16-bit, −32768 through 32767). Syntax:

1
SMALLINT(numeric-or-string-expression)

• Numeric arguments are converted; the fractional part is truncated toward zero (SMALLINT(1.9) is 1, SMALLINT(−1.9) is −1).
• Values outside the SMALLINT range raise an error (numeric overflow), not a wrap.
• A null argument yields a null result.
• Character strings that represent numbers are converted; invalid strings fail.

1
2
3
4
SELECT SMALLINT(EDLEVEL) AS ED_SI,
       SMALLINT(SALARY / 1000) AS SAL_K
  FROM DSN8C10.EMP
 WHERE EMPNO = '000010';

COBOL PIC S9(4) COMP (or COMP-5) maps to SMALLINT. Truncating SALARY/1000 into SMALLINT is a report trick, not a money calculation — keep DECIMAL for the real amount.

REAL

REAL returns a single-precision floating-point number (approximately 24 bits of significand). DOUBLE / FLOAT(53) is the 64-bit form. Syntax:

1
REAL(numeric-or-string-expression)

Use REAL when the source is already a measurement that is approximate (rates, scientific samples). Do not use REAL for currency, account balances, or anything that must match a pencil-and-paper decimal. Binary floating-point cannot store 0.10 exactly; DECIMAL can.

1
2
3
4
SELECT REAL(SALARY) AS SAL_REAL,     -- demonstration only; prefer DECIMAL for money
       DOUBLE(SALARY) AS SAL_DBL
  FROM DSN8C10.EMP
 WHERE EMPNO = '000010';

FLOAT(n) in DDL is REAL when n is 21 or less and DOUBLE when n is 22–53. The REAL function is the scalar equivalent of targeting that 32-bit format. Overflow and underflow follow floating-point rules; null in, null out.

QUARTER

QUARTER returns a large integer 1 through 4 for the calendar quarter of a date or timestamp.

1
QUARTER(expression)

• 1 — January, February, March
• 2 — April, May, June
• 3 — July, August, September
• 4 — October, November, December

The argument is a DATE, TIMESTAMP, or a valid string representation (not CLOB/DBCLOB, actual length ≤ 255 bytes). Fiscal quarters that start in April are not QUARTER — you compute those with CASE or a calendar table.

1
2
3
4
5
6
SELECT QUARTER(HIREDATE) AS HIRE_Q,
       YEAR(HIREDATE)    AS HIRE_Y,
       COUNT(*)          AS EMPS
  FROM DSN8C10.EMP
 GROUP BY YEAR(HIREDATE), QUARTER(HIREDATE)
 ORDER BY 2, 1;

WEEK

WEEK returns a large integer 1 through 54: the week of the year. Weeks start on Sunday. 1 January is always in week 1, so week 1 can be shorter than seven days.

1
WEEK(expression)

Same argument types as QUARTER. Timestamp-with-time-zone values use the UTC representation. Null in, null out.

1
2
3
SELECT WEEK(PRENDATE)
  FROM DSN8C10.PROJ
 WHERE PROJNO = 'AD2100';

WEEK_ISO

WEEK_ISO returns a large integer 1 through 53 using ISO-8601 rules:

• The week starts on Monday and has seven days.
• Week 1 is the first week of the year that contains a Thursday, which is equivalent to the first week that contains 4 January.
• The first one, two, or three days of January can belong to week 52 or 53 of the previous year. The last one, two, or three days of December can belong to week 1 of the next year.

1
2
3
4
5
SELECT DATECOL,
       WEEK(DATECOL)     AS US_WEEK,
       WEEK_ISO(DATECOL) AS ISO_WEEK
  FROM CALENDAR
 WHERE DATECOL BETWEEN '2026-01-01' AND '2026-01-07';

European and ISO reporting wants WEEK_ISO (often paired with a year-of-ISO-week calculation when January dates fall in the previous ISO year). US-style Sunday weeks that always include 1 January in week 1 use WEEK. Mixing them in one dashboard produces “off by one” fights every January.

Putting helpers in GROUP BY

QUARTER(HIREDATE) and WEEK_ISO(HIREDATE) are expressions. You can GROUP BY them. They are not sargable substitutes for a range predicate: WHERE QUARTER(HIREDATE) = 1 will not match an index on HIREDATE as well as WHERE HIREDATE BETWEEN '2026-01-01' AND '2026-03-31'. Prefer range predicates for filtering; use QUARTER/WEEK in the select list and GROUP BY for bucketing after the index has done its job.

Explain It Like I'm Five

SMALLINT is a small box that only holds whole numbers that are not too big — leftovers after the decimal point get thrown away. REAL is a box that holds “about this much” science numbers, not pocket money. QUARTER asks “which season-sized chunk of the calendar year is this date in?” WEEK numbers the year by Sunday-to-Saturday pages and always puts New Year’s Day on page 1. WEEK_ISO numbers the year the way international calendars do: weeks start Monday, and week 1 is the week that contains the first Thursday, so New Year’s Day can still be sitting on last year’s last page.

Exercises

1. Convert EDLEVEL to SMALLINT and explain what happens if a value is 40000.
2. State two reasons not to store SALARY as REAL.
3. Group DSN8C10.EMP by YEAR(HIREDATE) and QUARTER(HIREDATE) and count hires.
4. For the date 2026-01-01 (a Thursday), reason about WEEK versus WEEK_ISO (week 1 in both systems that year — then try 2016-01-01, a Friday, and explain ISO spill).
5. Rewrite SMALLINT(x) and REAL(x) as CAST expressions.