SA0058 : Avoid converting dates to string during date comparison

 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;