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

by

Why This Error Happens

This error occurs when the SQL Server DATEDIFF function attempts to calculate the difference between two date/time values using a datepart (such as milliseconds, microseconds, or nanoseconds) so precise that the resulting integer value exceeds the boundary of the int data type (2,147,483,647). Because DATEDIFF returns a signed 32-bit integer by default, calculating a fine-grained interval (like milliseconds) across a wide date range—such as several years—causes an arithmetic overflow. This is not a corruption issue, but rather a logical limitation in query design regarding data type capacity.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query

  • Check the SQL Server Error Log or Activity Monitor to find the specific T-SQL statement generating Error 535.
  • Look specifically for DATEDIFF functions using millisecond (ms), microsecond (mcs), or nanosecond (ns) as the datepart parameter.
  • Note the date ranges being compared; overflows typically happen when the date range spans months or years while using a high-precision unit.

Step 2: Switch to the DATEDIFF_BIG Function

  • Replace standard DATEDIFF calls with DATEDIFF_BIG in the T-SQL script. This function returns a bigint instead of an int, accommodating much larger values.
  • Example fix: Change SELECT DATEDIFF(millisecond, @StartDate, @EndDate) to SELECT DATEDIFF_BIG(millisecond, @StartDate, @EndDate).
  • Verify that any variables storing the result are updated to the BIGINT data type to prevent truncation errors downstream.

Step 3: Reduce Datepart Precision (If Applicable)

  • If DATEDIFF_BIG is not suitable for the specific business logic, modify the query to use a less granular datepart (e.g., switch from millisecond to second or minute).
  • Update application connection strings or stored procedures if the change impacts data type handling on the client side (e.g., .NET or Java drivers expecting an Int32).

Step 4: Update Client Drivers and Compatibility Level

  • Ensure the database compatibility level is set to 130 or higher, as DATEDIFF_BIG requires this level to execute correctly.
  • Run sp_dbcmptlevel [DatabaseName], 160 (or higher) in SQL Server Management Studio (SSMS) to align with SQL Server 2025 features on Windows Server 2025.

Alternative or Verification Steps

  • Verification: Re-run the specific query or stored procedure that triggered Error 535 in SSMS; a successful execution returning a large bigint value (without an error) confirms the fix.
  • Alternative: If modifying the query isn’t immediately possible, implement application-level logic to catch the exception and break the date calculation into smaller segmented ranges (e.g., calculating differences year-by-year and summing the results) to stay within int limits.