How to fix Error 2741 on Windows Server 2025 / Windows 11

by

Troubleshooting Guide: SQL Server Error 2741 — “SET DATEFORMAT date order ‘%.*ls’ is invalid”

Why This Error Happens

This error occurs when the SET DATEFORMAT statement receives an argument that does not match one of SQL Server’s valid date order values (mdy, dmy, ymd, ydm, myd, dym). It is typically caused by a mismatched regional/locale setting passed from an application connection string, a corrupted or incorrectly configured language setting in sys.syslanguages, or malformed input originating from a client driver (ODBC/OLE DB) that submits an unsupported locale string to the server. In rare cases, this can also surface after an in-place upgrade to SQL Server 2025 where legacy language metadata was not migrated correctly.

Step-by-Step Resolution Guide

Step 1: Validate the DATEFORMAT Syntax in Your Query or Application

  • Open the query, stored procedure, or application code issuing the SET DATEFORMAT command.
  • Confirm the value passed is one of the six valid strings: mdy, dmy, ymd, ydm, myd, dym — case and quotes matter.
  • Example of correct syntax: SET DATEFORMAT ymd;
  • Check if the value is being dynamically generated (e.g., from a config file or environment variable) and log the actual string being passed.

Step 2: Inspect Client Connection Language and Locale Settings

  • Open SQL Server Management Studio (SSMS) and check the default language assigned to the login under Security > Logins > [Login Name] > General.
  • Run the following to verify valid language/date order mappings:
  SELECT name, alias, date_format FROM sys.syslanguages;
  • Ensure the client connection string (ODBC, OLE DB, or ADO.NET) is not injecting a regional format incompatible with SQL Server, such as a full locale code (en-US) instead of a date order (mdy).
  • On Windows Server 2025 / Windows 11, check Control Panel > Region > Administrative > Change system locale to confirm it aligns with the SQL Server instance’s default language.

Step 3: Repair or Reset Corrupted Language Metadata

  • If sys.syslanguages appears corrupted or incomplete, restart the SQL Server instance in single-user mode via sqlcmd -S servername -m.
  • Run DBCC CHECKDB against the master database to rule out system catalog corruption:
  DBCC CHECKDB('master') WITH NO_INFOMSGS, ALL_ERRORMSGS;
  • If corruption is confirmed, restore master from a known-good backup or rebuild system databases using the SQL Server 2025 setup media with the /ACTION=REBUILDDATABASE switch.

Step 4: Update SQL Server Client Drivers and Cumulative Updates

  • Verify the ODBC/OLE DB driver version in use matches SQL Server 2025 compatibility requirements (minimum driver version 18.x or later).
  • Download the latest cumulative update (CU) for SQL Server 2025 from the Microsoft Update Catalog and apply it, as early builds had known locale-parsing defects in the date formatting module.
  • Restart the SQL Server service after patching via services.msc or net stop MSSQLSERVER && net start MSSQLSERVER.

Alternative or Verification Steps

  • After applying fixes, re-run the original query with an explicit, hardcoded valid value (e.g., SET DATEFORMAT dmy;) and confirm no Error 2741 is thrown in the SSMS Messages tab or SQL Server Error Log (Management > SQL Server Logs).
  • As a workaround, bypass application-level date formatting entirely by using the unambiguous yyyy-MM-ddTHH:mm:ss ISO 8601 format in all date literals, which eliminates dependency on SET DATEFORMAT altogether.