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

Using system date-time functions directly in SQL statements can lead to inconsistent query results and performance issues.

Description

Queries that incorporate system date-time functions like GetDate, GetUtcDate, or SysDateTime can create problems when executed at different times within the same transaction or process. This inconsistency arises because each invocation of a function might return a different value, affecting query logic and data manipulation outcomes.

For example:

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

In the example above, the value of @Now remains consistent throughout the script, ensuring that both the SELECT and DELETE operations use the same point in time for their comparisons. However, if GetDate were used directly in these statements, differing time values could cause inconsistencies and unexpected results, especially after long-running operations.

  • Inconsistent results when system date-time functions are invoked in different statements within the same transaction.

  • Potential performance degradation due to the dynamic computation of date-time values for each query execution.

How to fix

To ensure consistent query results and improve performance when using system date-time functions in SQL scripts, consider storing the function result in a local variable. This method ensures the same date-time value is used across multiple queries within the script.

Follow these steps to address the issue:

  1. Identify the system date-time function calls, such as GetDate(), GetUtcDate(), or SysDateTime(), within your SQL script.

  2. Extract the date-time function call and store its result into a local variable at the beginning of your script or before its first use. Declare a variable using DECLARE and assign the current date-time value to this variable.

  3. Use the variable instead of the direct function call in all subsequent SQL queries in the script. This ensures that all operations utilize the same point in time for calculations and comparisons.

For example:

SQL
Copy
 1-- Example of corrected use with a local variable
 2DECLARE @Now AS DateTime = GetDate();
 3
 4-- Using the variable in queries
 5SELECT * FROM dbo.Log WHERE DateCreated < @Now - 5;
 6
 7-- Perform time-consuming operations safely
 8WAITFOR DELAY '01:30:15';
 9
10-- Use the variable for consistency in time-dependent operations
11DELETE 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.

Effort To Fix
5 minutes per issue.
Categories

Design Rules, Bugs

Additional Information
Example Test SQL
SQL
Copy
1SELECT DATEPART(MONTH, SYSDATETIMEOFFSET());
2
3SELECT DATEPART(MONTH, SYSUTCDATETIME());
4
5SELECT DATEPART(MONTH, CURRENT_TIMESTAMP);
6
7SELECT DATEPART(MONTH, GETUTCDATE());
8
9SELECT DATEPART(MONTH, GETDATE());
Analysis Results
  Message Line Column
1 SA0133 : Consider storing the SYSDATETIMEOFFSET() function result in a variable at the beginning of the statement and using that variable. 1 23
2 SA0133 : Consider storing the SYSUTCDATETIME() function result in a variable at the beginning of the statement and using that variable. 3 23
3 SA0133 : Consider storing the CURRENT_TIMESTAMP function result in a variable at the beginning of the statement and using that variable. 5 23
4 SA0133 : Consider storing the GETUTCDATE() function result in a variable at the beginning of the statement and using that variable. 7 23
5 SA0133 : Consider storing the GETDATE() function result in a variable at the beginning of the statement and using that variable. 9 23
See Also

Other Resources

Analysis Rules