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.

The rule looks for these date functions:

  • GetDate

  • GetUtcDate

  • SysDateTime

  • SysDateTimeOffset

  • SysUtcDatetime

  • Current_Timestamp

Example:

SQL
 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

How to fix

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.

Scope

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

Parameters

Rule has no parameters.

Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix
5 minutes per issue.
Categories

Design Rules, Bugs

Additional Information
Example Test SQL
SQL
 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
  Message Line Column
1 SA0133 : Consider storing the GETDATE() function result in a variable at the beginning of the statement and using that variable. 7 5
2 SA0133 : Consider storing the GETDATE() function result in a variable at the beginning of the statement and using that variable. 19 16
3 SA0133 : Consider storing the GETDATE() function result in a variable at the beginning of the statement and using that variable. 20 51
4 SA0133 : Consider storing the GETDATE() function result in a variable at the beginning of the statement and using that variable. 29 23
5 SA0133 : Consider storing the GETDATE() function result in a variable at the beginning of the statement and using that variable. 30 23
6 SA0133 : Consider storing the GETDATE() function result in a variable at the beginning of the statement and using that variable. 31 32
7 SA0133 : Consider storing the SYSDATETIME() function result in a variable at the beginning of the statement and using that variable. 34 23
8 SA0133 : Consider storing the SYSDATETIMEOFFSET() function result in a variable at the beginning of the statement and using that variable. 36 23
9 SA0133 : Consider storing the SYSUTCDATETIME() function result in a variable at the beginning of the statement and using that variable. 38 23
10 SA0133 : Consider storing the CURRENT_TIMESTAMP function result in a variable at the beginning of the statement and using that variable. 40 23
See Also

Other Resources