DB2 datetime functions: DATE, DAY, YEAR, ADD_MONTHS and LAST_DAY

Constructors: DATE, TIME, TIMESTAMP

DATE(expression) returns a date. The argument can already be a date, a timestamp (the date part is taken), a valid string representation of a date or timestamp, or a numeric day-count that DATE can interpret per the function rules. TIME(expression) returns a time from a time, timestamp, or valid string. TIMESTAMP(expression) or TIMESTAMP(date-expression, time-expression) builds a timestamp.

1
2
3
4
5
6
SELECT DATE('2000-01-15') AS D1,
       DATE(CURRENT TIMESTAMP) AS D2,
       TIME('12.30.00') AS T1,
       TIMESTAMP(CURRENT DATE, TIME('13.00.00')) AS TS1,
       TIMESTAMP_ISO(CURRENT DATE) AS TS_ISO
  FROM SYSIBM.SYSDUMMY1;

TIMESTAMP_ISO(expression) returns a timestamp with the ISO-oriented combination of date and time. If the argument is a date, the time part is midnight. If it is a time, the date part is the current date. It is a convenience constructor when you need a timestamp from a partial value.

TIMESTAMP WITH TIME ZONE values are first cast to TIMESTAMP WITHOUT TIME ZONE (same precision) for many of these functions, so the zone is not kept unless you use zone-aware functions such as TIMESTAMP_TZ. String arguments that are not valid representations raise an error, not a null — unless the argument itself is null, in which case the result is null.

Extractors: YEAR, MONTH, DAY and the rest

1
2
3
4
5
6
7
8
SELECT YEAR(HIREDATE) AS Y,
       MONTH(HIREDATE) AS M,
       DAY(HIREDATE) AS D,
       DAYOFWEEK(HIREDATE) AS DOW,
       DAYOFYEAR(HIREDATE) AS DOY,
       DAYS(HIREDATE) AS SERIAL
  FROM DSN8C10.EMP
 FETCH FIRST 5 ROWS ONLY;

• DAY versus DAYOFMONTH — both give the day within the month for date and timestamp arguments. DAY also has rules for durations and other datetime flavours; when you mean “calendar day of month,” either name is read clearly. Prefer one in a shop standard and stick to it.
• DAYOFWEEK — 1 Sunday through 7 Saturday. DAYOFWEEK_ISO uses Monday = 1 through Sunday = 7. Mixing them in the same report is a classic off-by-one.
• HOUR, MINUTE, SECOND, MICROSECOND — extract from TIME or TIMESTAMP. SECOND can take an integer precision argument in some forms to control fractional seconds. MICROSECOND is the fractional millionths; timestamp precision beyond microseconds needs the timestamp precision features of your function level.
• DAYS — integer serial day count from 1 January 0001. Elapsed whole days: DAYS(SHIPDATE) − DAYS(ORDERDATE). That pattern is in IBM’s own shipping-delay examples and avoids converting dates to character strings.
• JULIAN_DAY — another serial numbering (astronomical Julian day). Do not assume JULIAN_DAY − JULIAN_DAY is the only way to get elapsed days; DAYS is the Db2-native business choice.

EXTRACT(field FROM source) is the SQL-standard spelling for many of the same parts. YEAR(x) and EXTRACT(YEAR FROM x) are the two styles you will see. Function names YEAR / MONTH / DAY are everywhere in z/OS COBOL shops.

ADD_MONTHS, LAST_DAY, NEXT_DAY

ADD_MONTHS

ADD_MONTHS(expression, numeric-expression) returns the starting date or timestamp plus that many months. The integer portion of the numeric argument is the month count. Negative values go backward. If the start is the last day of a month, or the target month has fewer days than the start day, the result is the last day of the target month. Time-of-day on a timestamp is preserved.

1
2
3
4
SELECT ADD_MONTHS(DATE('2008-01-31'), 1) AS JAN_TO_FEB,
       ADD_MONTHS(DATE('2008-03-31'), -1) AS MAR_TO_FEB,
       ADD_MONTHS(CURRENT DATE, 6) AS PLUS_SIX
  FROM SYSIBM.SYSDUMMY1;

January 31 plus one month is February 28 or 29, never an invalid February 31. That end-of-month rule is why ADD_MONTHS is safer than blindly adding 1 to MONTH() and keeping DAY() unchanged.

LAST_DAY

LAST_DAY(expression) returns the last day of the month of the argument. Dates stay dates; timestamps stay timestamps (time-of-day kept) unless the argument was a string, which is cast toward DATE. TIMESTAMP WITH TIME ZONE is cast to without time zone first.

1
2
3
SELECT LAST_DAY(DATE('2008-02-15')) AS END_FEB,
       LAST_DAY(CURRENT DATE) AS END_THIS_MONTH
  FROM SYSIBM.SYSDUMMY1;

NEXT_DAY

NEXT_DAY(expression, string-expression) returns the first weekday named by the string that is strictly later than expression. Portable values are full English day names or abbreviations (MON, TUE, …). If expression is a date, the result time is midnight. If it is a timestamp, the time-of-day is kept.

1
SET :vNEXTDAY = NEXT_DAY(LAST_DAY(CURRENT DATE), :vDAYOFWEEK);

IBM’s example sets the first Monday after the last day of the current month — that is the first Monday of next month when you pass 'MON'. NEXT_DAY does not mean “the Monday of this week.”

MONTHS_BETWEEN and elapsed time

MONTHS_BETWEEN(start, end) returns a DECIMAL number of months between two datetime values, including a fraction. It is an estimate for age-in-months style math, not a guarantee that ADD_MONTHS(start, ROUND(that result)) lands on end. For whole days, use DAYS subtraction. For labeled durations, CURRENT DATE - BIRTHDATE yields a duration you can feed to YEAR() for age in years (the classic YEAR(CURRENT DATE - BIRTHDATE) pattern).

1
2
3
4
5
SELECT EMPNO,
       YEAR(CURRENT DATE - BIRTHDATE) AS AGE_YEARS,
       DAYS(CURRENT DATE) - DAYS(HIREDATE) AS DAYS_EMPLOYED
  FROM DSN8C10.EMP
 FETCH FIRST 5 ROWS ONLY;

TIMESTAMP_FORMAT and string layouts

TIMESTAMP_FORMAT(string-expression, format-string) (also TO_TIMESTAMP) parses a string that is not necessarily in the default timestamp format. An optional precision constant sets fractional-second precision (default 6 in the usual form). Format elements include YYYY, MM, DD, HH24, MI, SS, and others documented in the SQL Reference.

1
2
SELECT TIMESTAMP_FORMAT('2002-05-01 12:00:00', 'YYYY-MM-DD HH24:MI:SS')
  FROM SYSIBM.SYSDUMMY1;

TO_DATE is a related spelling in some Db2 family products. On z/OS, TIMESTAMP_FORMAT is the name to learn. CHAR(timestamp, ISO) goes the other way (timestamp to string). VARCHAR_FORMAT / TO_CHAR format timestamps as strings when you need a custom layout in the result.

TIMEZONE and CURRENT TIMEZONE

CURRENT TIMEZONE is a special register: a time duration that is the difference between UTC and local time for the Db2 subsystem. You add it to UTC-based timestamps or subtract it, according to the direction you need, when the column is without time zone. TIMESTAMP WITH TIME ZONE columns store an offset; TIMESTAMP_TZ builds that type.

There is no MONTH-style TIMEZONE(expression) extractor on z/OS that returns “EST” as a string. When a topic list says TIMEZONE under datetime functions, read it as “timezone support”: CURRENT TIMEZONE, timestamp with time zone, and TIMESTAMP_TZ.

XMLVALIDATE is not a datetime function

XMLVALIDATE(xml-expression ACCORDING TO XMLSCHEMA ...) checks an XML value against a registered XML schema. DSN_XMLVALIDATE is a related supplied function. Failures are XML schema errors, not SQL datetime errors. Use the XML functions page for constructors and validation.

Indexes and wrapping columns

YEAR(HIREDATE) = 2000 typically cannot use a simple index on HIREDATE the way HIREDATE BETWEEN '2000-01-01' AND '2000-12-31' can. Prefer range predicates on the raw column for year/month filters. Functions in the SELECT list are free for display.

Explain It Like I'm Five

A date is a labeled calendar page. YEAR, MONTH, and DAY are reading the three numbers printed in the corner. DAYS is numbering every page from a giant book that started at year 1, so subtracting two page numbers tells you how many nights passed. ADD_MONTHS flips a number of pages forward but is smart when a month is too short — it will not invent January 32. LAST_DAY jumps to the last page of that month. NEXT_DAY flips forward until the weekday sticker you asked for (Monday, Tuesday, …) shows up — always a future sticker, never yesterday’s.

Exercises

1. List employees hired in 2000 using a range on HIREDATE, then using YEAR(HIREDATE). Which one is friendlier to an index?
2. Compute LAST_DAY(HIREDATE) and the number of days remaining in that hire month (LAST_DAY minus HIREDATE).
3. Predict ADD_MONTHS(DATE('2023-01-31'), 1) and ADD_MONTHS(DATE('2023-01-30'), 1). Explain the difference.
4. Write NEXT_DAY so you always get the following Friday after CURRENT DATE, even when today is Friday.
5. Express “orders more than five days late” with DAYS(SHIPDATE) − DAYS(ORDERDATE) and explain why CHAR dates would be the wrong tool.