SA0058 : Avoid converting dates to string during date comparison

The topic describes the SA0058 analysis rule.

Message

Avoid converting dates to string during date comparison

Description

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

How to fix

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

Scope

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

Parameters
Name Description Default Value
MatchInWhereAndJoinOnly

Match the date conversions only in WHERE or JOIN clauses.

yes

Remarks

The rule requires SQL Connection. If there is no connection provided, the rule will be skipped during analysis.

Effort To Fix
20 minutes per issue.
Categories

Design Rules, Bugs

Additional Information

There is no additional info for this rule.

Example Test SQL
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;

Analysis Results
  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
See Also

Other Resources