SA0058 : Avoid converting dates to string during date comparison |
![]() |
Comparing dates by converting them to strings can lead to inefficient queries, as it prevents the use of indexes and introduces potential errors due to format mismatches.

Comparing dates through conversion to string using the CONVERT function can lead to inefficient queries and unexpected results. This approach is problematic because it bypasses the optimized date handling capabilities of SQL Server, which may result in performance degradation and incorrect comparisons.
For example:
1-- Example of problematic query 2SELECT * 3FROM Orders 4WHERE CONVERT(VARCHAR, OrderDate, 101) = '12/31/2020';
In this example, the OrderDate column is converted to a string before comparison, which can lead to a full table scan and is susceptible to format mismatches. Direct date comparisons without conversion are preferred to improve query efficiency and accuracy.
-
Performance impact: Using string conversion can prevent the query optimizer from using indexes effectively, leading to slower query execution.
-
Potential inaccuracies: Different date formats and language settings can cause errors or unexpected results in comparisons.

Optimize date comparisons by avoiding string conversions and using native date functions like DATEADD and DATEDIFF for efficiency and accuracy.
Follow these steps to address the issue:
-
Analyze the original query to identify any use of CONVERT function for date comparisons, which can lead to inefficient execution plans.
-
Replace the conversion and string comparison with a direct date comparison. Ensure that both sides of the comparison are datetime data types.
-
If necessary, utilize functions like DATEADD and DATEDIFF to manipulate and compare date values directly in their native format.
For example:
1-- Example of corrected query 2SELECT * 3FROM Orders 4WHERE OrderDate = '2020-12-31';

The rule has a Batch scope and is applied only on the SQL script.

Name | Description | Default Value |
---|---|---|
MatchInWhereAndJoinOnly |
Match the date conversions only in WHERE or JOIN clauses. |
yes |

The rule requires Analysis Context. If context is missing, the rule will be skipped during analysis.


Design Rules, Bugs

There is no additional info for this rule.

SQL
1DECLARE @date2 AS DATETIME = GETDATE() 2 3SELECT CONVERT( VARCHAR( 20 ), StartDate, 103 ) AS StartDate 4FROM vEmployeeDepartment 5WHERE CONVERT( VARCHAR( 20 ), @date2, 103 ) <= CONVERT( VARCHAR( 20 ), StartDate, 103 ) 6 7UPDATE vEmployeeDepartment SET StartDate = DATEADD( d, 1, StartDate ) 8WHERE CONVERT( VARCHAR( 20 ), @date2, 103 ) <= '2010-03-13 00:00:12.000' |

Message | Line | Column | |
---|---|---|---|
1 | SA0058 : Avoid converting dates to string during date comparison. | 5 | 7 |
2 | SA0058 : Avoid converting dates to string during date comparison. | 5 | 48 |
3 | SA0058 : Avoid converting dates to string during date comparison. | 8 | 7 |
