apache/shardingsphere · error · SQLFeatureNotSupportedException

Can not support date format if year, month, day is absent.

Error message

Can not support date format if year, month, day is absent.

What it means

Thrown when reading a MySQL binary-protocol DATE/DATETIME value whose stored length byte is 0. Length 0 encodes the MySQL 'zero date' 0000-00-00 (or a NULL-ish date); this reader cannot represent it as a java.sql.Timestamp, so it throws SQLFeatureNotSupportedException. It is a checked-style JDBC exception, so callers can branch on it explicitly.

Source

Thrown at database/protocol/dialect/mysql/src/main/java/org/apache/shardingsphere/database/protocol/mysql/packet/command/query/binary/execute/protocol/MySQLDateBinaryProtocolValue.java:42

import java.sql.SQLFeatureNotSupportedException;
import java.sql.Timestamp;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.Date;

/**
 * Binary protocol value for date for MySQL.
 */
public final class MySQLDateBinaryProtocolValue implements MySQLBinaryProtocolValue {
    
    private static final long NANOS_PER_SECOND = 1_000_000_000L;
    
    @Override
    public Object read(final MySQLPacketPayload payload, final boolean unsigned) throws SQLException {
        int length = payload.readInt1();
        switch (length) {
            case 0:
                throw new SQLFeatureNotSupportedException("Can not support date format if year, month, day is absent.");
            case 4:
                return getTimestampForDate(payload);
            case 7:
                return getTimestampForDatetime(payload);
            case 11:
                Timestamp result = getTimestampForDatetime(payload);
                result.setNanos(payload.readInt4() * 1000);
                return result;
            default:
                throw new SQLFeatureNotSupportedException(String.format("Wrong length `%d` of MYSQL_TYPE_TIME", length));
        }
    }
    
    private Timestamp getTimestampForDate(final MySQLPacketPayload payload) {
        return Timestamp.valueOf(LocalDate.of(payload.readInt2(), payload.readInt1(), payload.readInt1()).atStartOfDay());
    }
    
    private Timestamp getTimestampForDatetime(final MySQLPacketPayload payload) {

View on GitHub (pinned to e952770a21)

Solutions

  1. Set the server sql_mode to include NO_ZERO_DATE,NO_ZERO_IN_DATE,STRICT_TRANS_TABLES and repair existing rows: UPDATE t SET d=NULL WHERE d='0000-00-00' (or a real date)
  2. Rewrite zero dates in the data before querying through the proxy
  3. Select NULL instead of zero dates by rewriting the query (e.g. NULLIF(d,'0000-00-00'))
  4. If compatibility with zero dates is required, request text protocol results rather than server-side prepare, or handle SQLFeatureNotSupportedException per column

Example fix

-- before: server allows zero dates
SET GLOBAL sql_mode='';

-- after: forbid and clean them
SET GLOBAL sql_mode='STRICT_TRANS_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE';
UPDATE events SET occurred_at = NULL WHERE occurred_at = '0000-00-00';
Defensive patterns

Strategy: try-catch

Validate before calling

-- prevent at the source: forbid zero dates
SET SESSION sql_mode = CONCAT(@@session.sql_mode, ',NO_ZERO_DATE,NO_ZERO_IN_DATE');

-- or neutralize them in the query so the binary reader never sees length 0
SELECT NULLIF(date_col, '0000-00-00') AS date_col FROM t;

Try / catch

try {
    rs = preparedStatement.executeQuery();
} catch (SQLFeatureNotSupportedException ex) {
    if (ex.getMessage().contains("year, month, day is absent")) {
        // zero-date row in binary protocol: clean the data or select NULLIF(...) and retry once
        throw new DataQualityException("zero date values present; clean them or use NULLIF", ex);
    }
    throw ex;
}

Prevention

When it happens

Trigger: Executing a prepared statement in binary mode where a DATE/DATETIME column contains '0000-00-00' — typical when the server's sql_mode lacks NO_ZERO_DATE/STRICT mode and legacy rows exist. MySQLDateBinaryProtocolValue.read() sees length 0.

Common situations: Legacy schemas with zero dates; servers with sql_mode='' ; migrations importing old dumps that re-enable zero dates; test fixtures using '0000-00-00' as a sentinel.

Related errors

AI-assisted analysis of apache/shardingsphere@e952770a21 (2026-08-14). Data as JSON: /api/errors/0caab7c3043d1565. Report an issue: GitHub.