SQLCODE -180 / -181 — invalid date, time or timestamp

What happened

An insert, update or query with date logic fails for some records but not others. The message names the data type that was rejected.

DSNT408I SQLCODE = -180, ERROR:  THE DATE, TIME, OR TIMESTAMP VALUE
         2026/13/01 IS INVALID
DSNT418I SQLSTATE   = 22007 SQLSTATE RETURN CODE

What it means

Db2 stores dates as real date values. When it receives a string it must convert it. -180 (SQLSTATE 22007) means the string's syntax is wrong: wrong separators, wrong length, letters, spaces. -181 (SQLSTATE 22008) means the syntax is fine but the value is impossible, such as month 13 or 30 February.

SQLERRMC usually carries the data type or the rejected value. It does not name the column, so the program must log the statement and key.

Typical causes

• The host variable holds spaces or low-values because a field was never filled.
• Dates built from YYYYMMDD fields without inserting the separators.
• A format that does not match what Db2 accepts at your site (ISO, USA, EUR, JIS or a site LOCAL format).
• Placeholder values such as 0000-00-00 or 9999-99-99 from old files.
• Wrong arithmetic producing day 32 or month 0.

Symptoms

Fails only for certain records. Often the bad values come from one source system. It differs from a COBOL S0C7 because Db2 catches it and returns an SQLCODE instead of an abend.

Where to look

• The program's error log with SQLCODE, value and key.
• The input record in a dataset browse with HEX ON to spot spaces or low-values.
• The source or listing to see how the date string is built.
• Site Db2 settings or the precompiler DATE and TIME options, which control the output format; input strings are accepted in any IBM standard format (ISO, USA, EUR, JIS), plus LOCAL only if a date/time exit is installed.

How to diagnose

1. Note -180 or -181 and the rejected value or data type.
2. Identify the statement and record key from the program log.
3. Look at the raw input value in hex.
4. Compare the format with what your site accepts; ISO YYYY-MM-DD is the safe choice.
5. For -181, check the value is a real calendar date or time.

How to fix

Fix the bad source data with the data owner, or correct how the program builds the string. For optional dates, use a null indicator to store NULL instead of a fake date, if the column allows it. Then rerun or restart. To turn the raw SQLCA into readable text, call DSNTIAR (DSNTIAC in CICS) with the SQLCA and a message buffer, or use GET DIAGNOSTICS to fetch MESSAGE_TEXT, RETURNED_SQLSTATE and DB2_RETURNED_SQLCODE. Either way, write the full message to the job log before the program ends.

How to prevent

• Validate dates in COBOL before SQL, for example with intrinsic functions such as TEST-DATE-YYYYMMDD where available.
• Always build dates in ISO format.
• Use null indicators for unknown dates instead of placeholder values.
• Agree data rules with upstream systems.

Production considerations

Interview question

What is the difference between SQLCODE -180 and -181?