alibaba/DataX · error · SQLException

Date 类型转换错误：[%s]

Error message

Date 类型转换错误：[%s]

What it means

Raised in AdsInsertProxy while binding a DATE column in a prepared INSERT: if column.asDate() throws DataXException (the Column cannot be interpreted as a date), it is wrapped as SQLException 'Date 类型转换错误：[<column>]'. The bracketed part is the offending Column's toString, so it shows the raw value and type that failed.

Source

Thrown at adswriter/src/main/java/com/alibaba/datax/plugin/writer/adswriter/insert/AdsInsertProxy.java:550

                Long longValue = column.asLong();
                if (null == longValue) {
                    statement.setNull(preparedPatamIndex + 1, Types.BIGINT);
                } else {
                    statement.setLong(preparedPatamIndex + 1, longValue);
                }

                break;

            case Types.DATE:
                java.sql.Date sqlDate = null;
                try {
                    if ("".equals(column.getRawData())) {
                        utilDate = null;
                    } else {
                        utilDate = column.asDate();
                    }
                } catch (DataXException e) {
                    throw new SQLException(String.format(
                            "Date 类型转换错误：[%s]", column));
                }

                if (null != utilDate) {
                    sqlDate = new java.sql.Date(utilDate.getTime());
                }
                statement.setDate(preparedPatamIndex + 1, sqlDate);
                break;

            case Types.TIME:
                java.sql.Time sqlTime = null;
                try {
                    if ("".equals(column.getRawData())) {
                        utilDate = null;
                    } else {
                        utilDate = column.asDate();
                    }
                } catch (DataXException e) {

View on GitHub (pinned to 80ec23d5c5)

Solutions

  1. Copy the failing value from the exception message and test its format against the expected date format.
  2. If the source column is a string, set the correct date format in the reader/writer column config or trim/clean the data so values are empty or valid dates.
  3. Check that the job's column order matches the ADS table definition (a shifted column makes a non-date value land on the DATE column).
  4. Add a transformer or dirty-data handling to route unparseable values to the side channel instead of failing the task.

Example fix

# before: source row
2023-13-45,alice
# after (clean or route dirty rows)
2023-04-15,alice
Defensive patterns

Strategy: try-catch

Validate before calling

static final Pattern DATE_RE = Pattern.compile("\\d{4}-\\d{2}-\\d{2}");
boolean isParseableDate(Column c) {
    String raw = c == null ? null : c.asString();
    return raw == null || "".equals(raw) || DATE_RE.matcher(raw.trim()).matches();
}

Try / catch

try {
    proxy.fillStatement(...);
} catch (SQLException e) {
    if (e.getMessage() != null && e.getMessage().contains("Date 类型转换错误")) {
        // route row to dirty-data collector, keep task alive
        dirtyCollector.collect(currentRecord, e.getMessage());
        return;
    }
    throw e;
}

Prevention

When it happens

Trigger: Filling a prepared statement for a target column of java.sql.Types.DATE where the incoming Column is e.g. a StringColumn whose content is not parseable as a date, or a numeric/bytes column coerced via asDate().

Common situations: Dirty source data ('2023-13-45', 'N/A', trailing spaces), a reader delivering dates as strings in a format the Column's asDate() cannot parse, or a column-order/type mismatch between the job config's column list and the ADS table schema.

Related errors

AI-assisted analysis of alibaba/DataX@80ec23d5c5 (2026-08-14). Data as JSON: /api/errors/8d093db66a89e67d. Report an issue: GitHub.