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:

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

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.

Categories

Design Rules, Bugs

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