SA0058 : Avoid converting dates to string during date comparison |
![]() |
The topic describes the SA0058 analysis rule.

Avoid converting dates to string during date comparison

The rule checks T-SQL script for date comparison made with using conversion to string (the CONVERT function).

Consider using DATEADD and DATEDIFF functions as the to string conversion can lead to incorrect results.

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 SQL Connection. If there is no connection provided, the rule will be skipped during analysis.


Design Rules, Bugs

There is no additional info for this rule.

SQL
1CREATE PROCEDURE RuleSampleProcedure 2( 3 @value1 AS INT 4 , @varcharParam AS VARCHAR( 10 ) 5 , @dateParam1 AS DATETIME 6 , @dateParam2 AS DATETIME 7) 8AS 9SELECT GETDATE() AS UnconvertedDateTime 10 , CAST( GETDATE() AS NVARCHAR( 30 ) ) AS UsingCast 11 , CONVERT( NVARCHAR( 30 ), GETDATE(), 126 ) AS UsingConvertTo_ISO8601; 12 13DECLARE @dateString AS VARCHAR( 123 ) 14DECLARE @date1 AS DATETIME 15DECLARE @date2 AS DATETIME 16DECLARE @sdate1 AS SMALLDATETIME 17DECLARE @sdate2 AS SMALLDATETIME 18 19SET @date2 = getdate( ) 20 21SET @dateString = '2010-03-13 10:00:12.000' 22 23SELECT * 24FROM vEmployeeDepartment 25WHERE ( DATEADD( dd, DATEDIFF( dd, 0, StartDate ), 0 ) <= DATEADD( dd, DATEDIFF( dd, 0, getdate( ) ), 0 ) ) 26 27SELECT * 28 , CONVERT( VARCHAR( 20 ), StartDate, 103 ) AS StartDate 29FROM vEmployeeDepartment 30WHERE CONVERT( VARCHAR( 20 ), @date2, 103 ) <= CONVERT( VARCHAR( 20 ), StartDate, 103 ) 31 32SELECT * 33FROM vEmployeeDepartment 34WHERE CONVERT( VARCHAR( 20 ), getdate( ), 103 ) <= CONVERT( VARCHAR( 20 ), StartDate1, 103 ) 35 36SELECT * 37FROM vEmployeeDepartment 38WHERE CONVERT( VARCHAR( 20 ), getdate( ), 103 ) <= '2010-03-13 00:00:12.000' 39 40SELECT * 41FROM vEmployeeDepartment 42WHERE CONVERT( VARCHAR( 20 ), @dateString, 103 ) <= '2010-03-13 00:00:12.000' 43 44DELETE 45FROM vEmployeeDepartment 46WHERE CONVERT( NVARCHAR( 30 ), GETDATE( ), 126 ) = CAST( GETDATE( ) AS NVARCHAR( 30 ) ); 47 48UPDATE vEmployeeDepartment 49SET StartDate = dateadd(d, 1, StartDate) 50WHERE CONVERT( VARCHAR( 20 ), @dateParam1, 103 ) <= '2010-03-13 00:00:12.000' 51 52SELECT * 53FROM vEmployeeDepartment 54WHERE CONVERT( VARCHAR( 20 ), @dateParam1, 103 ) <= '2010-03-13 00:00:12.000' 55 OR CAST( @dateParam1 AS VARCHAR( 20 ) ) <= '2010-03-13 00:00:12.000' 56 OR CAST( '2010-03-13 00:00:12.000' AS datetime ) > @dateParam1 57 58SELECT * 59 , CONVERT( VARCHAR( 20 ), StartDate, 103 ) AS StartDate 60FROM vEmployeeDepartment 61WHERE CONVERT(date, @date2 ) <= CONVERT( date, StartDate ) OR 62 CONVERT(date,'2010-03-13 00:00:12.000') = CONVERT( date, dateadd(d, -1, StartDate )) OR 63 CONVERT(date,@date2) = CONVERT( date, dateadd(d, -1, StartDate )) 64 65SELECT * 66 , CONVERT( VARCHAR( 20 ), StartDate, 103 ) AS StartDate 67FROM vEmployeeDepartment 68WHERE CONVERT( VARCHAR( 20 ), @date2, 103 ) <= CONVERT( VARCHAR( 20 ), StartDate, 103 ) /*IGNORE:SA0058*/ 69 OR CONVERT( VARCHAR( 20 ), @dateParam1, 103 ) <= CONVERT( VARCHAR( 20 ), StartDate, 103 ) 70 71MERGE INTO A_Table WITH (FASTFIRSTROW) 72USING 73 (SELECT 'data_searched' AS Search_Col FROM B_TABLE) AS SRC 74 ON A_Table.Data = SRC.Search_Col AND CONVERT(varchar(10), @date1, 100) = '2012/08/23' 75WHEN MATCHED THEN 76 UPDATE SET Data = 'data_searched_updated' 77WHEN NOT MATCHED THEN 78 INSERT (Data) VALUES (SRC.Search_Col); 79 80IF CONVERT(varchar(10), @date1, 100) = '2012/08/23' SELECT @date1; 81IF CAST(@date1 as varchar(10)) = '2012/08/23' SELECT @date1; |

Message | Line | Column | |
---|---|---|---|
1 | SA0058 : Avoid converting dates to string during date comparison. | 30 | 50 |
2 | SA0058 : Avoid converting dates to string during date comparison. | 30 | 50 |
3 | SA0058 : Avoid converting dates to string during date comparison. | 34 | 55 |
4 | SA0058 : Avoid converting dates to string during date comparison. | 50 | 55 |
5 | SA0058 : Avoid converting dates to string during date comparison. | 54 | 55 |
6 | SA0058 : Avoid converting dates to string during date comparison. | 55 | 52 |
7 | SA0058 : Avoid converting dates to string during date comparison. | 69 | 49 |
8 | SA0058 : Avoid converting dates to string during date comparison. | 69 | 49 |
