SQLCODE -180: Invalid date, time or timestamp string

What it means

Db2 stores DATE, TIME and TIMESTAMP columns in its own internal form, so programs pass them in and get them back as character strings. When Db2 turns a string into one of those types, it first checks the layout. The string has to start with a digit and have the separators in the right places. A date needs at least 8 characters and a four-digit year. Leading zeros in the month and day can be left out, and trailing blanks are ignored. If it doesn't fit any layout Db2 accepts, the statement fails with -180 and the message shows the value Db2 rejected.

For input, Db2 accepts ISO and JIS dates (2026-10-12), USA dates (10/12/2026) and EUR dates (12.10.2026), plus a LOCAL format if your site has installed a date exit. Times follow similar rules, such as 14.30.00 or 14:30:00. A timestamp is usually written 2026-10-12-14.30.00.000000.

-180 is about the shape of the string, not whether the date is real. 2026/10/12 and 20261012 both fail, because neither matches a date layout. When the shape is fine but the date can't exist, such as month 13, Db2 returns -181 instead.

Common causes

• The host variable holds spaces or low-values because the input field was blank or the program never moved a date into it. This is the most common cause by far.
• The date uses separators Db2 doesn't accept for that layout, such as 2026/10/12 or 12-10-2026.
• The string is the wrong length, for example a two-digit year like 10/12/26, or a PIC X(8) field holding 20261012.
• A CHAR or VARCHAR column that stores dates as text is converted with DATE() or compared with a DATE, and some rows hold text that isn't a date.
• The program builds the date by stringing year, month and day fields together, and one piece is blank or the wrong length.

How to find the problem

1. Read the value in the DSNT408I message or SQLERRMC. If it looks empty, the host variable held spaces or low-values.
2. Display the date host variable in hex just before the statement runs. X'40' bytes are spaces and X'00' bytes are low-values.
3. If the statement has several date or time host variables, match the value in the message to the one that holds it.
4. For a query over a text column, look for bad rows with a check on the separator positions, for example rows where the fifth character isn't a hyphen.

How to fix it

• Move a valid date into the field before the SQL runs. For a missing date in a nullable column, set the indicator variable to -1 so Db2 stores a null instead.
• If the column is NOT NULL, agree on a default such as 0001-01-01 or 9999-12-31 and set it in the program. Never send blanks.
• Convert dates from other systems into ISO form, yyyy-mm-dd in a PIC X(10) field, before they reach Db2.
• Validate input dates in the program before the INSERT or UPDATE, so one bad record is rejected and reported instead of stopping the run.
• For a text column that holds dates, clean up the bad rows, or change the column to DATE so Db2 stops bad values getting in.

What it looks like

CUSTLD01 - INSERT CUST_ACCT FAILED, RECORD 004812
DSNT408I SQLCODE = -180, ERROR:  THE DATE, TIME, OR TIMESTAMP VALUE 2026/10/12 IS
         INVALID
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.