SQL Server: 'Conversion failed when converting date and/or time from character string' (error 241)

Conversion failed when converting date and/or time from character string. (error 241) means SQL Server could not read a date written as text. Most often the text is fine but in the wrong order for the session: '31/10/2026' is day-first, and a session in us_english reads month-first, so month 31 does not exist.

Write date literals as ISO '2026-10-31' (or '20261031'), and from C# pass a DateTime as a typed parameter instead of pasting text into the SQL. SET DATEFORMAT dmy also works, as a last resort for one script.

The error

PS> .\FixLab.exe 'Server=(localdb)\MSSQLLocalDB;Database=fix7_lab;Integrated Security=true' "INSERT INTO dbo.Invoices (CustomerName, DueDate) VALUES ('Maria Garcia', '31/10/2026')"
ex.Number = 241, ex.Class = 16, ex.State = 1
Microsoft.Data.SqlClient.SqlException: Conversion failed when converting date and/or time from character string.

Why it happens

A string becomes a date according to the session's language, which sets its date order. In the test database fix7_lab the session reported us_english and mdy. After SET LANGUAGE British the same insert of '31/10/2026' succeeded, and '10/31/2026' failed with the same error 241 instead. Nothing about the data changed, only who read it, which is why this error shows up when a script moves to a server or login with a different default language.

The C# version of the mistake is building the SQL text with a formatted date. With the app's culture set to en-GB, {dueDate:d} printed 31/10/2026 and the insert failed exactly as above:

CultureInfo.CurrentCulture = new CultureInfo("en-GB");
var dueDate = new DateTime(2026, 10, 31);
var sql = $"INSERT INTO dbo.Invoices (CustomerName, DueDate) VALUES ('Maria Garcia', '{dueDate:d}')";

The fix

using var cmd = new SqlCommand(
    "INSERT INTO dbo.Invoices (CustomerName, DueDate) VALUES (@name, @dueDate)", conn);
cmd.Parameters.Add("@name", SqlDbType.NVarChar, 100).Value = "Maria Garcia";
cmd.Parameters.Add("@dueDate", SqlDbType.Date).Value = dueDate;

Same culture, same date, and the insert went through (OK (1 row(s) affected)): the parameter travels as a date value, so no text is ever parsed. In SQL scripts, the ISO form worked under both languages:

PS> .\FixLab.exe 'Server=(localdb)\MSSQLLocalDB;Database=fix7_lab;Integrated Security=true' "INSERT INTO dbo.Invoices (CustomerName, DueDate) VALUES ('David Chen', '2026-10-31'); SET LANGUAGE British; INSERT INTO dbo.Invoices (CustomerName, DueDate) VALUES ('Aiko Tanaka', '2026-10-31'); SELECT Id, CustomerName, DueDate FROM dbo.Invoices ORDER BY Id"
info: Changed language setting to British.
3 | Maria Garcia | 2026-10-31
5 | David Chen | 2026-10-31
6 | Aiko Tanaka | 2026-10-31
OK (2 row(s) affected) in 0.3 s

One trap remains: the dashed form is safe for date and datetime2, not for the old datetime. Under British, CAST('2026-10-31' AS datetime) failed with error 242, "The conversion of a varchar data type to a datetime data type resulted in an out-of-range value", while '20261031' and '2026-10-31T00:00:00' converted fine.

To find the bad rows in imported text, TRY_CONVERT returns NULL instead of failing, and style 103 reads day-first explicitly:

PS> .\FixLab.exe 'Server=(localdb)\MSSQLLocalDB;Database=fix7_lab;Integrated Security=true' "CREATE TABLE dbo.InvoiceImport (CustomerName NVARCHAR(100) NOT NULL, DueDateText VARCHAR(20) NOT NULL); INSERT INTO dbo.InvoiceImport VALUES ('Sofia Rossi', '2026-10-31'), ('Kwame Mensah', '31/10/2026'), ('Lena Novak', '30/02/2026'); SELECT CustomerName, DueDateText, TRY_CONVERT(date, DueDateText) AS AsDefault, TRY_CONVERT(date, DueDateText, 103) AS AsStyle103 FROM dbo.InvoiceImport"
Sofia Rossi | 2026-10-31 | 2026-10-31 | NULL
Kwame Mensah | 31/10/2026 | NULL | 2026-10-31
Lena Novak | 30/02/2026 | NULL | NULL

A row that is NULL in both columns, like 30 February, is not a date in any order.

How it was reproduced

A console app from dotnet new console -n FixLab -f net10.0 (.NET SDK 10.0.403) with Microsoft.Data.SqlClient 7.1.1, against SQL Server 2022 LocalDB 16.0.1210.3 with a Windows login whose default language is us_english. The table dbo.Invoices has a DATE column. Ids 1, 2 and 4 are missing above because failed inserts still use identity values.

Frequently asked

Which date format is always safe in SQL Server?

In this test 2026-10-31 worked for date and datetime2 under both us_english and British, but failed for datetime under British. 20261031 and 2026-10-31T00:00:00 converted to datetime under British too, so use those for datetime.

Why does the same date insert work on one SQL Server and fail on another?

Text dates are read in the session's date order, which comes from the login's default language. A us_english session reads month first, a British one day first.

How do I find rows with invalid dates in a varchar column?

Select the rows where TRY_CONVERT(date, column) IS NULL. Add a style such as 103 for day-first text to tell a wrong order from a date that does not exist.

The same family of error for numbers is error 245, varchar to int. More decoded errors in the Fixes category.