Oninit
Talk to an expert →

Informix Error -1277

-1277 Input does not match format specification.

Check that the ASCII string that contains a DATETIME or INTERVAL value conforms to the format string. For example, a percent character in a DATETIME or INTERVAL ASCII string must have a matching "%%" sequence in the format string. See also the discussion of the DBTIME environment variable in the IBM Informix Guide to SQL: Reference.

If you are using the ROUND() or TRUNC() build-in functions on DATE or DATETIME data types, note that the list of supported values for the second parameter is "YEAR", "MONTH", "DAY", "DD", "HH" or "MI". Please refer to the discussion on "Algebraic Functions" in the IBM Informix Guide to SQL: Syntax for the details.

Oninit® Troubleshooting Guidance

Reasons / Common Causes

-1277 fires when an ASCII string holding a DATETIME/INTERVAL value doesn't conform to the specified format string — per the official guidance, a literal % in the ASCII string must have a matching %% sequence in the format string. The same message also covers ROUND()/TRUNC() on DATE/DATETIME being given a second parameter other than YEAR, MONTH, DAY, DD, HH, or MI.

  1. A literal % in the input string without a matching %% in the format string, per the official guidance — the direct, named cause.
  2. ROUND()/TRUNC() given an unsupported second-parameter value on DATE/DATETIME, per the official guidance's other named case — only YEAR, MONTH, DAY, DD, HH, MI are valid.
  3. The input string's actual structure not matching the format string's expected fields, more generally.
Solutions / Resolution
  1. Escape any literal % in the input with a matching %% in the format string, per the official guidance.
  2. Use only YEAR/MONTH/DAY/DD/HH/MI as ROUND()/TRUNC()'s second parameter, per the official guidance, when operating on DATE/DATETIME.
Examples
An unsupported ROUND() unit
SELECT ROUND(order_datetime, 'SECOND') FROM orders WHERE order_id = 1;
-- -1277: 'SECOND' isn't one of the supported units
Corrected
SELECT ROUND(order_datetime, 'MI') FROM orders WHERE order_id = 1;
Diagnostic Checks
  1. Check whether this is a ROUND()/TRUNC() call, and if so, confirm the second parameter is one of the six supported values.
  2. Otherwise, compare the input string against the format string, checking specifically for an unescaped literal %.
Related Errors / Related Topics
  • -1275 — "Invalid field width or precision in datetime or interval format string." A related format-string error, about field-width/precision syntax rather than input/format mismatch.
  • -1276 — "Format conversion character not supported." A related format-string error, about an unsupported conversion character.

Either an unescaped % in the input string, or an unsupported ROUND()/TRUNC() unit — check both cases.