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

Introduction

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:

-- Example of problematic query
DECLARE @Now AS DateTime = GetDate();
-- later in the script
SELECT * FROM dbo.Log WHERE DateCreated < @Now - 5;
-- something time consuming happens here
WAITFOR DELAY '01:30:15';
-- another time-dependent operation
DELETE 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:

-- Example of corrected use with a local variable
DECLARE @Now AS DateTime = GetDate();

-- Using the variable in queries
SELECT * FROM dbo.Log WHERE DateCreated < @Now - 5;

-- Perform time-consuming operations safely
WAITFOR DELAY '01:30:15';

-- Use the variable for consistency in time-dependent operations
DELETE 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.

Additional Information

Date and Time Data Types and Functions (Transact-SQL)

Example Test SQL

SELECT DATEPART(MONTH, SYSDATETIMEOFFSET());

SELECT DATEPART(MONTH, SYSUTCDATETIME());

SELECT DATEPART(MONTH, CURRENT_TIMESTAMP);

SELECT DATEPART(MONTH, GETUTCDATE());

SELECT 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

Analysis Rules