// SQL Server error reference

SQL Server error 241: Conversion failed for date and/or time

A string couldn't be converted to a date or time: wrong format for the session's language and DATEFORMAT, or not a real date.

Error number
241
Severity
16
Category
Data conversion and truncation
Error message
Conversion failed when converting date and/or time from character string.

What it means

SQL Server parses date strings according to the format and the session's DATEFORMAT and LANGUAGE settings. If the text isn't a valid date in that interpretation ('31/12/2025' under a month-first setting, '2025-02-30', 'yesterday'), conversion to date, datetime or datetime2 fails with 241.

Common causes

  • Ambiguous formats like '03/04/2025' or '31/12/2025', read differently depending on SET DATEFORMAT and the login's language.
  • Dates that don't exist, such as February 30.
  • Free text or empty strings in a column that should hold dates.
  • Using CONVERT with a style number that doesn't match the string.

How to fix it

  1. Use unambiguous ISO formats: '20251231' or '2025-12-31T14:30:00'. These parse the same under every language and DATEFORMAT setting (with datetime2 and date, '2025-12-31' is also safe).

    SELECT CAST('20251231' AS date), CAST('2025-12-31T14:30:00' AS datetime2);
  2. Parse known formats with an explicit style. Style 103 is dd/mm/yyyy, 101 is mm/dd/yyyy.

    SELECT CONVERT(date, '31/12/2025', 103);
  3. Find the bad values with TRY_CONVERT.

    SELECT RawDate FROM staging.Events WHERE TRY_CONVERT(date, RawDate, 103) IS NULL AND RawDate IS NOT NULL;
  4. Pass dates from applications as typed parameters (date, datetime2), never as formatted strings.

Reproduce it safely

SET DATEFORMAT mdy;
SELECT CAST('31/12/2025' AS date);  -- Msg 241

Related errors

More data conversion and truncation errors

The code and message text are from the Microsoft Database Engine events and errors reference. Browse the full SQL Server error list or search every database error.

// fewer errors, faster fixes

Work on SQL Server in SQLly

A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for SQL Server and the other databases you use.