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
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 onSET DATEFORMATand 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
CONVERTwith a style number that doesn't match the string.
How to fix it
-
Use unambiguous ISO formats:
'20251231'or'2025-12-31T14:30:00'. These parse the same under every language and DATEFORMAT setting (withdatetime2anddate,'2025-12-31'is also safe).SELECT CAST('20251231' AS date), CAST('2025-12-31T14:30:00' AS datetime2); -
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);
-
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;
-
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
242Out-of-range date conversion245Conversion failed converting a value22007PostgreSQL: invalid datetime format22008PostgreSQL: date/time field value out of range1292MySQL: Truncated incorrect valueORA-01843Oracle: not a valid monthORA-01861Oracle: literal does not match format string
More data conversion and truncation errors
8152String or binary data truncated2628Truncated in table, column8115Arithmetic overflow error converting8114Error converting data type512Subquery returned more than 1 value8134Divide by zero error encountered220Arithmetic overflow error for data type248Conversion overflowed an int column
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.
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.