pentaho/pentaho-kettle · error · KettleValueException

: couldn't convert string [" + string + "] to a timestamp…

Error message

 : couldn't convert string [" + string + "] to a timestamp, expecting format [yyyy-mm-dd hh:mm:ss.ffffff]

What it means

Thrown by ValueMetaTimestamp.convertStringToTimestamp when a string cannot be parsed into a java.sql.Timestamp. It first tries Timestamp.valueOf(), then falls back to the value metadata's configured date format; if both fail a KettleValueException is raised. The message embeds the offending string and the expected format yyyy-mm-dd hh:mm:ss.ffffff.

Solutions

Normalize the string to yyyy-mm-dd hh:mm:ss[.ffffff] before passing it in
Set an explicit conversion mask (setConversionFormat / format in the field meta) matching the input so getDateFormat() can parse it
Pre-parse with a Select Values / 'String to date' step using the correct mask
Verify no stray whitespace, 'T' separators, or timezone suffixes in the source data

Example fix

// before
row.get("ts") // "2024-01-05T10:00:00"
// after
String s = row.getString("ts").replace('T', ' ');
valueMeta.setConversionFormat("yyyy-MM-dd HH:mm:ss.ffffff");
Timestamp ts = valueMeta.getTimestamp(s);

Defensive patterns

Strategy: validation

Validate before calling

String s = value == null ? null : value.toString().trim().replace('T', ' ');
if (s != null && !s.matches("\\d{4}-\\d{2}-\\d{2} \\d{2}:\\d{2}:\\d{2}(\\.\\d{1,9})?")) {
  throw new IllegalArgumentException("Timestamp string not in yyyy-mm-dd hh:mm:ss[.ffffff]: " + s);
}

Try / catch

try {
  ts = valueMetaTimestamp.getTimestamp(data);
} catch (KettleValueException e) {
  logError("Bad timestamp string: " + e.getMessage());
  ts = null; // or route row to error stream
}

Prevention

Always set an explicit conversion format on timestamp fields read from text
Strip 'T' separators and timezone suffixes before conversion
Use testConvertStringToTimestamp in unit tests against real sample data
Keep date/time fields in Date/Timestamp objects end-to-end, not strings

When it happens

Trigger: Calling getTimestamp/convertData/testConvertStringToTimestamp with a String whose content does not match either 'yyyy-[m]m-[d]d hh:mm:ss[.f...]' (Timestamp.valueOf) or the configured DateFormat, e.g. '2024-01-05T10:00:00' (ISO 'T' separator) or a locale-formatted date.

Common situations: CSV/text inputs with alternate date formats, ISO-8601 strings from external APIs, missing fractional seconds or timezone suffixes, locale-dependent formats like '05/01/2024 10:00'.

Related errors

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

Appendix: source

Thrown at core/src/main/java/org/pentaho/di/core/row/value/ValueMetaTimestamp.java:272

    return timestamp;
  }

  protected synchronized Timestamp convertStringToTimestamp( String string ) throws KettleValueException {
    // See if trimming needs to be performed before conversion
    //
    string = Const.trimToType( string, getTrimType() );

    if ( Utils.isEmpty( string ) ) {
      return null;
    }
    Timestamp returnValue;
    try {
      returnValue = Timestamp.valueOf( string );
    } catch ( IllegalArgumentException e ) {
      try {
        returnValue = (Timestamp) getDateFormat().parse( string );
      } catch ( ParseException ex ) {
        throw new KettleValueException( toString() + " : couldn't convert string [" + string
          + "] to a timestamp, expecting format [yyyy-mm-dd hh:mm:ss.ffffff]", e );
      }
    }
    return returnValue;
  }

  protected synchronized String convertTimestampToString( Timestamp timestamp ) throws KettleValueException {

    if ( timestamp == null ) {
      return null;
    }

    return getDateFormat().format( timestamp );
  }

  @Override
  public Object convertDataFromString( String pol, ValueMetaInterface convertMeta, String nullIf, String ifNull,
                                       int trimType ) throws KettleValueException {

View on GitHub (pinned to f3058517a1)