What causes INVALID_DATE_TIME_FORMAT (100096)?

Inserting a date string in MM/DD/YYYY format when Snowflake's DATE_INPUT_FORMAT is YYYY-MM-DD. TO_TIMESTAMP() called without an explicit format mask on non-ISO strings. Mixed date formats in a column (some rows ISO, some localized) causing partial failures. Epoch milliseconds passed to TO_TIMESTAMP() without specifying the scale (TO_TIMESTAMP(ms_val, 3)). Source system changed date format without downstream schema update

How do I fix INVALID_DATE_TIME_FORMAT (100096)?

Use explicit format masks: TO_DATE(my_col, 'MM/DD/YYYY') or TO_TIMESTAMP(my_col, 'YYYY-MM-DD HH24:MI:SS').. Set session-level format: ALTER SESSION SET DATE_INPUT_FORMAT = 'MM/DD/YYYY'.. Use TRY_TO_DATE() to return NULL on unparseable values without failing the query.. For epoch timestamps: TO_TIMESTAMP(epoch_ms / 1000) or TO_TIMESTAMP(epoch_ms, 3).. Audit the source column for format inconsistencies before loading: SELECT DISTINCT date_col FROM staging LIMIT 100.

Medium severityexecutionSnowflake →

Snowflake Error:
INVALID_DATE_TIME_FORMAT (100096)

What does this error mean?

Snowflake cannot parse a string value as a date, time, or timestamp because the value's format does not match the expected format — either the session default or the format specified in TO_DATE / TO_TIMESTAMP.

Common causes

1Inserting a date string in MM/DD/YYYY format when Snowflake's DATE_INPUT_FORMAT is YYYY-MM-DD
2TO_TIMESTAMP() called without an explicit format mask on non-ISO strings
3Mixed date formats in a column (some rows ISO, some localized) causing partial failures
4Epoch milliseconds passed to TO_TIMESTAMP() without specifying the scale (TO_TIMESTAMP(ms_val, 3))
5Source system changed date format without downstream schema update

How to fix it

1Use explicit format masks: TO_DATE(my_col, 'MM/DD/YYYY') or TO_TIMESTAMP(my_col, 'YYYY-MM-DD HH24:MI:SS').
2Set session-level format: ALTER SESSION SET DATE_INPUT_FORMAT = 'MM/DD/YYYY'.
3Use TRY_TO_DATE() to return NULL on unparseable values without failing the query.
4For epoch timestamps: TO_TIMESTAMP(epoch_ms / 1000) or TO_TIMESTAMP(epoch_ms, 3).
5Audit the source column for format inconsistencies before loading: SELECT DISTINCT date_col FROM staging LIMIT 100.

Frequently asked questions

How do I handle multiple date formats in the same column?

Use COALESCE with multiple TRY_TO_DATE() calls: COALESCE(TRY_TO_DATE(col, 'YYYY-MM-DD'), TRY_TO_DATE(col, 'MM/DD/YYYY'), TRY_TO_DATE(col, 'DD-MON-YYYY')). The first successful parse wins.

What is the default Snowflake date input format?

The default DATE_INPUT_FORMAT is AUTO, which accepts ISO 8601 formats and some common variations. For strict control, always specify the format explicitly in TO_DATE() rather than relying on AUTO.

Source · docs.snowflake.com/en/sql-reference/errors