SQLCODE -181: Date or time value out of range

What it means

Db2 checks a datetime string in two stages. First it checks the layout, meaning the length and the separators. A failure there is -180. If the layout is right, Db2 then checks each part against the calendar and the clock. The month must be 01 to 12, the day must exist in that month and year, the hour must be 00 to 24, and minutes and seconds must be 00 to 59. A failure at this second stage is -181.

The calendar check is complete. February 29 is valid only in a leap year, and April, June, September and November have no day 31. Db2 dates run from 0001-01-01 to 9999-12-31, so year 0000 fails too. A TIME of 24.00.00 is accepted as midnight at the end of the day, but anything past it, such as 24.00.01, is not.

The -181 message doesn't include the value, unlike -180. You need the program's own output, or a look at the input record, to see which value Db2 rejected.

Common causes

• Day and month are swapped. A file written in day-month-year order and read as month-day-year turns 25/12/2026 into month 25.
• A placeholder date such as 0000-00-00 or 2026-00-00, often copied from another database or used for unknown dates.
• Date arithmetic done by hand in the program, such as adding 1 to the day, produces a day that doesn't exist, like April 31 or February 29 in a non-leap year.
• The program builds dates from separate year, month and day fields, and one of them was never set or holds zero.
• A time such as 24.30.00 or 12.60.00 from a source system that writes times differently.

How to find the problem

1. Display the date or time host variables just before the failing statement, since the message won't show the value.
2. Check the input record being processed when the error happened. Look at the month and day positions to see if they're swapped or zero.
3. If the dates come from a file, scan the whole file for impossible values before loading it, for example with a sort step that selects records whose month is outside 01 to 12.
4. In SQL that converts text to dates, find bad rows by checking the month part with SUBSTR on the text column.

How to fix it

• Fix the bad value at its source and rerun. If a file arrives in another date order, convert it to ISO yyyy-mm-dd before it reaches Db2.
• Replace placeholder dates with a null (indicator -1) or with an agreed default such as 0001-01-01.
• Let Db2 do date arithmetic. DATE(:WS-START) + 1 MONTH or CURRENT DATE + 30 DAYS handles month lengths and leap years for you.
• Validate dates in the program before the SQL runs. The Language Environment service CEEDAYS returns an error for a date that doesn't exist.

What it looks like

ORDLD02 - UPDATE ORDER_HDR FAILED, SHIP DATE 2026-04-31
DSNT408I SQLCODE = -181, ERROR:  THE STRING REPRESENTATION OF A DATETIME VALUE IS NOT
         A VALID DATETIME VALUE
DSNT418I SQLSTATE   = 22007 SQLSTATE RETURN CODE

Last reviewed 2026-10-12 by the WND Software team. Written in our own words. For the full reference, check IBM’s documentation for your z/OS, CICS or Db2 release.