SSRS报表DateTime参数丢失毫秒问题：Visual Studio预览正常，部署后无查询结果

Hey Robin, I’ve run into this exact SSRS datetime parameter issue before—let’s break down what’s happening and share better solutions than your current workaround.

This isn’t exactly a "bug" but a well-documented behavior in SSRS. When you deploy your report, the server-side handling of DateTime parameters truncates milliseconds by default. SSRS’s built-in DateTime type maps closely to SQL Server’s older datetime type (which only supports precision up to 3.33ms), so any millisecond values beyond that get stripped when the parameter is passed to your query.

Let’s skip the slow per-row calculation and use these more efficient fixes:

1. Switch to DateTime2 for Parameters and Database Fields #

If you have control over both the report and database schema:

• In your SSRS report, edit the parameter’s data type to DateTime2 (found in the parameter properties under "General").
• Update your database column from datetime to datetime2 (if possible). datetime2 supports full millisecond precision, so the parameter value will pass through intact, matching your stored records exactly.
• If you can’t change the database field type, using DateTime2 parameters still ensures the full millisecond value is sent to SQL Server—just make sure your query compares against the field correctly (since datetime will implicitly convert to datetime2 without losing data).

2. Use a Range Query (Best for Large Datasets) #

If changing schema isn’t an option, adjust your query to match a time range instead of an exact value. This lets the database use indexes on your ILRReturnDate field, avoiding the performance hit of your current workaround.

Replace your exact match:

FD.ILRReturnDate = @ILRReturnDate

With a range that covers the full second (including all milliseconds in that window):

FD.ILRReturnDate >= @ILRReturnDate 
AND FD.ILRReturnDate < DATEADD(SECOND, 1, @ILRReturnDate)

This works because even if SSRS truncates the parameter to the second, the range will capture all records with that second’s timestamp—including those with non-zero milliseconds. And since we’re not applying functions to the FD.ILRReturnDate field, the database can use any existing indexes for fast lookups.

Your current approach of stripping milliseconds from every row with DATEADD(MS, -DATEPART(MS, FD.ILRReturnDate), FD.ILRReturnDate) forces the database to perform a full table scan. It can’t use indexes on ILRReturnDate because you’re modifying the field value before comparing it—this is a classic anti-pattern for large datasets.

Yes, this is a widely reported behavior in SSRS. The root cause is the mismatch between SSRS’s DateTime parameter precision and SQL Server’s datetime/datetime2 field types. Most users resolve it with either the DateTime2 switch or range query method above.

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