Click or drag to resize

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).

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
NameDescriptionDefault Value
RuleSuppressMark

Suppress rule reporting a rule violation in case a comment containing the value of this parameter is found close to the source of violation.

IGNORE:SA0058

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.

Categories

Design Rules

Additional Information

There is no additional info for this rule.

Example Test Script
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

 MessageLineColumn
1SA0058 : Avoid converting dates to string during date comparison.3050
2SA0058 : Avoid converting dates to string during date comparison.3050
3SA0058 : Avoid converting dates to string during date comparison.3455
4SA0058 : Avoid converting dates to string during date comparison.5055
5SA0058 : Avoid converting dates to string during date comparison.5455
6SA0058 : Avoid converting dates to string during date comparison.5552
7SA0058 : Avoid converting dates to string during date comparison.6949
8SA0058 : Avoid converting dates to string during date comparison.6949
See Also

Other Resources