DECLARE vs. Direct DateTime Conversion Performance Comparison

December 18, 2023

I am trying to understand if there is any performance difference between declaring a datetime conversion variable and using it versus directly converting the date in a SQL Server query.

Here are two example queries:
Query 1: Do DateTime Conversion Every Time

SELECT * 
FROM TestMessages
WHERE CreatedDate > CONVERT(DATETIME, '2023-12-18 00:00:00', 120);

Query 2: DECLARE DateTime Conversion Variable

DECLARE @yourDateString NVARCHAR(19) = '2023-12-18 00:00:00';
DECLARE @ConvertedDate DATETIME = CONVERT(DATETIME, @yourDateString, 120);

SELECT * 
FROM TestMessages
WHERE CreatedDate > @ConvertedDate;

Is there any noticeable performance difference between these two queries? How does declaring a variable for datetime conversion impact the execution time of the query? Additionally, what SQL Server tools or techniques can be used to analyze and measure this performance difference?

I’ve been experimenting with two different approaches in my SQL Server queries for datetime conversion. In one query, I directly convert the datetime value, and in another, I use DECLARE to create a conversion variable. I expected to observe a potential difference in performance between these two methods, but the results were not as clear as I anticipated. I would like insights on the performance implications of these approaches and any recommended practices for optimizing datetime conversions in SQL Server queries.

>Solution :

With

WHERE CreatedDate > CONVERT(DATETIME, '2023-12-18 00:00:00', 120);

the cardinality estimates will likely be more accurate.

This will improve the chances of getting an appropriate plan and, potentially, memory grant.

For the CreatedDate > @ConvertedDate predicate it will just assume 30% will match unless you also use OPTION (RECOMPILE) to allow it to sniff the variable’s value.

For CreatedDate > CONVERT(DATETIME, '2023-12-18 00:00:00', 120) it can look that value up in the histogram and get estimates from there.

CREATE TABLE TestMessages
             (
                          CreatedDate DATETIME INDEX ix_CreatedDate,
                          Filler      CHAR(1000) NULL
             )
INSERT TestMessages
       (CreatedDate)
SELECT DATEADD(HOUR, value, '1990-01-01 00:00:00')
FROM   generate_series(1, 300000)

WHERE CreatedDate > CONVERT(DATETIME, ‘2023-12-18 00:00:00’, 120);