pentaho/pentaho-kettle · error · KettleValueException

TO_DATE Couldn't convert String to Date

Error message

TO_DATE Couldn't convert String to Date

What it means

StringUtil.str2dat converts a string to a java.util.Date using a SimpleDateFormat with the given pattern; if df.parse fails (unparseable or mismatched format), it wraps the exception in a KettleValueException with message "TO_DATE Couldn't convert String to Date " plus e.toString(). This mirrors the SQL TO_DATE function semantics used in Kettle formula/expression evaluation.

Solutions

  1. Make the input format match the pattern passed to str2dat/TO_DATE exactly (check day/month order, separators, and time components).
  2. Log/inspect the offending value and fix the upstream source to emit a consistent format.
  3. Use lenient parsing or pre-clean the string (trim, remove time zones, map month names) before conversion.
  4. Handle null/empty inputs before calling TO_DATE.

Example fix

// before
Date d = StringUtil.str2dat("13/09/2026", "yyyy-MM-dd");
// after
Date d = StringUtil.str2dat("13/09/2026", "dd/MM/yyyy");
Defensive patterns

Strategy: validation

Validate before calling

// validate the string parses before str2dat
SimpleDateFormat probe = new SimpleDateFormat(format);
probe.setLenient(false);
probe.parse(value); // throws ParseException early if bad

Try / catch

try {
  Date d = StringUtil.str2dat(value, format);
} catch (KettleValueException e) {
  log.error("Unparseable date value: " + value + " with pattern " + format);
  d = null; // or route the row to an error stream
}

Prevention

When it happens

Trigger: Calling StringUtil.str2dat(value, format) where value does not match the pattern arg0 (wrong date format string, localized text, extra whitespace, or a completely non-date string).

Common situations: CSV or database extract delivering dates as 'dd/MM/yyyy' while the pattern is 'yyyy-MM-dd'; locale-dependent month names (e.g. 'Jan' vs 'Januar'); empty or null-ish strings passed to TO_DATE in expressions.

Related errors

AI-assisted analysis of pentaho/pentaho-kettle@f3058517a1 (2026-09-13). Data as JSON: /api/errors/76f181744a5179fa. Report an issue: GitHub.

Appendix: source

Thrown at core/src/main/java/org/pentaho/di/core/util/StringUtil.java:486

  public static final boolean isEmpty( StringBuilder string ) {
    return string == null || string.length() == 0;
  }

  public static Date str2dat( String arg0, String arg1, String val ) throws KettleValueException {
    SimpleDateFormat df = new SimpleDateFormat();

    DateFormatSymbols dfs = new DateFormatSymbols();
    if ( arg1 != null ) {
      dfs.setLocalPatternChars( arg1 );
    }
    if ( arg0 != null ) {
      df.applyPattern( arg0 );
    }

    try {
      return df.parse( val );
    } catch ( Exception e ) {
      throw new KettleValueException( "TO_DATE Couldn't convert String to Date " + e.toString() );
    }
  }

  public static String getIndent( int indentLevel ) {
    return INDENTCHARS.substring( 0, indentLevel );
  }

  /**
   * Giving back a date/time string in the format following the rule from the most to the least significant
   *
   * @param date
   *          the date to convert
   * @return a string in the form yyyddMM_hhmmss
   */
  public static String getFormattedDateTime( Date date ) {
    return getFormattedDateTime( date, false );
  }

View on GitHub (pinned to f3058517a1)