SQL与C#中调用同一存储过程返回结果不一致问题排查

Let’s break down this common discrepancy—it’s almost never a problem with your stored procedure itself, but rather how .NET handles SQL data types or subtle differences in execution context. Here’s what’s going on and how to fix it:

1. ADO.NET’s Default Type Mapping for SQL DATE #

SQL Server’s DATE type is strictly date-only, but prior to .NET 6, there was no native DateOnly type in .NET. So when ADO.NET reads a DATE value from SQL, it automatically maps it to a DateTime object—which always includes a time component (defaulting to 00:00:00).

When you run the stored procedure in SSMS, it displays the DATE value as just the date (since SSMS recognizes it’s a date-only type). But in your DataTable, the column is of type DateTime, so when you inspect the value, you’ll see the trailing 00:00:00 time. This is just a type representation quirk—the underlying date value is still correct.

Fixes for This: #

• If using .NET 6+: Use SqlDataReader.GetDateOnly() to read the value, then store it in a DataTable column of type DateOnly. This eliminates the time component entirely.
• For older .NET versions: When accessing the value from the DataTable, use the DateTime.Date property to get the date-only part, or format it for display with a string like row["OpenedDate"].ToString("yyyy-MM-dd").

2. Accidental Context or Procedure Mismatch #

Double-check that your C# code is calling the exact same stored procedure in the same database as you’re testing in SSMS. It’s easy to accidentally reference a different environment (e.g., staging vs. production) or an outdated version of the procedure that doesn’t include the CAST(loan.OpenedDate AS DATE) conversion.

To verify:

• Run EXEC sp_helptext 'YourStoredProcedureName' in both SSMS and from your C# code (via SqlCommand) to confirm the procedure definitions match.
• Check your C# connection string to ensure it points to the correct server and database.

3. Post-Load Data Modification #

Is there any code in your app that modifies the OpenedDate column after loading it into the DataTable? For example, logic that sets the time component explicitly, or converts the column type to something else.

Inspect your code flow: after filling the DataTable with SqlDataAdapter.Fill(), look for any lines that update row["OpenedDate"] or alter the column’s data type.

4. Rare DataReader/Adapter Configuration Quirks #

While uncommon, custom configurations for SqlDataAdapter or SqlDataReader can affect how values are read. For example, a custom type handler might be converting the DATE value to a DateTime with a non-default time component.

To rule this out, create a minimal test: write a simple console app that connects to the database, executes the stored procedure, reads the OpenedDate value directly via SqlDataReader, and logs its type and value. This will isolate whether the issue is in DataTable loading or elsewhere.

内容的提问来源于stack exchange，提问作者Robert