Click or drag to resize

SA0133 : Consider storing the result of the Date-Time function which get current time in a variable at the beginning of the statement and use these variable later

The topic describes the SA0133 analysis rule.

Message

Consider storing the result of the Date-Time function which get current time in a variable at the beginning of the statement and use these variable later

Description

The rule checks for direct usage of the system date-time functions which return the current date and time inside SELECT, INSERT, UPDATE, DELETE and MERGE statements.

Consider extracting the function call and storing its result into a local variable and this way ensure that one and the same current time is used for all queries in the script.

The rule looks for these date functions:

  • GetDate

  • GetUtcDate

  • SysDateTime

  • SysDateTimeOffset

  • SysUtcDatetime

  • Current_Timestamp

Example:

 1DECLARE @Now AS DateTime = GetDate()
 2
 3-- later in the script
 4SELECT * FROM dbo.Log WHERE DateCreated < @Now - 5
 5
 6-- something time consuming happens here
 7WAITFOR DELAY '01:30:15';
 8
 9--- 
10DELETE FROM dbo.Log WHERE DateCreated < @Now - 5

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:SA0133

Remarks

The rule does not need Analysis Context or SQL Connection.

Categories

Design Rules

Additional Information
Example Test Script
 1DECLARE @Now AS DateTime = GetDate()
 2DECLARE @Now1 AS DateTime
 3
 4SET @Now1 = GetDate()
 5SELECT @Now1 = GetDate()
 6
 7IF ( GetDate() > '2012-08-30 00:00:00')
 8BEGIN
 9        PRINT 'Some message'
10END
11
12WHILE ( GetDate() > '2012-08-30 00:00:00') /*IGNORE:SA0133(LINE)*/
13BEGIN
14        PRINT GetDate()
15END
16
17SELECT OrderId, OrderNumber, Created
18FROM Orders
19WHERE fnGetDate(getdate()) >'2010/09/01' OR 
20      DATEADD(day,2, getdate()/*IGNORE:SA0133*/) > getDate() 
21
22DECLARE @Date datetime
23SET @Date = GetDate()
24SELECT * FROM Orders WHERE fnGetDate(@Date) >'2010/09/01' 
25
26DECLARE @postDateTime DATETIME
27SET @postDateTime = CONVERT( DATETIME, CONVERT( CHAR(20), GETDATE(), 113 ));
28
29SELECT DATEPART(MONTH, GETDATE());
30SELECT DATEPART(MONTH, GETDATE()) AS Month2;
31SELECT Month2 = DATEPART(MONTH, GETDATE());
32
33-- Should trigger SA0133 as date function is not set in a variable at the start.
34SELECT DATEPART(MONTH, SYSDATETIME());
35-- Should trigger SA0133 as date function is not set in a variable at the start.
36SELECT DATEPART(MONTH, SYSDATETIMEOFFSET());
37-- Should trigger SA0133 as date function is not set in a variable at the start.
38SELECT DATEPART(MONTH, SYSUTCDATETIME());
39-- Should trigger SA0133 as date function is not set in a variable at the start.
40SELECT DATEPART(MONTH, CURRENT_TIMESTAMP);
41-- Should trigger SA0133 as date function is not set in a variable at the start.
42SELECT DATEPART(MONTH, GETUTCDATE());

Analysis Results

 MessageLineColumn
1SA0133 : Consider storing the GETDATE() function result in a variable at the beginning of the statement and using that variable.75
2SA0133 : Consider storing the GETDATE() function result in a variable at the beginning of the statement and using that variable.1916
3SA0133 : Consider storing the GETDATE() function result in a variable at the beginning of the statement and using that variable.2051
4SA0133 : Consider storing the GETDATE() function result in a variable at the beginning of the statement and using that variable.2923
5SA0133 : Consider storing the GETDATE() function result in a variable at the beginning of the statement and using that variable.3023
6SA0133 : Consider storing the GETDATE() function result in a variable at the beginning of the statement and using that variable.3132
7SA0133 : Consider storing the SYSDATETIME() function result in a variable at the beginning of the statement and using that variable.3423
8SA0133 : Consider storing the SYSDATETIMEOFFSET() function result in a variable at the beginning of the statement and using that variable.3623
9SA0133 : Consider storing the SYSUTCDATETIME() function result in a variable at the beginning of the statement and using that variable.3823
10SA0133 : Consider storing the CURRENT_TIMESTAMP function result in a variable at the beginning of the statement and using that variable.4023
...
See Also

Other Resources