Why assigning and printing count from a query gives a different result in SQL Server?

WinFXGuy 0
Viewed 28

I have the following query:

SELECT COUNT(*) 
FROM Availability 
WHERE CONVERT(date, DayStartTime) = '9/1/2022 09:00 AM'

which returns a value of "1" in the SSMS query window.

But if I execute the following query, it prints "0"

DECLARE @i int
DECLARE @DayStartTime datetime = '9/1/2022 09:00 AM'

SELECT @i = COUNT(*) 
FROM Availability 
WHERE CONVERT(date, DayStartTime) = @DayStartTime

PRINT @i

Any reason? Thanks!

Update

Changing the query like this works.

DECLARE @i int
DECLARE @DayStartTime datetime = '9/1/2022 09:00 AM'
DECLARE @From DATETIME = CONVERT(DATE, @DayStartTime)
DECLARE @To DATETIME = DATEADD(DAY, 1, @From)

PRINT @From
PRINT @To

SELECT @i = COUNT(*) 
FROM Availability 
WHERE DayStartTime >= @From AND DayStartTime < @To

PRINT @i
tsql sql-server sql
1 Answers

In this query:

select count(*) from Availability where Convert(date,DayStartTime)='9/1/2022 09:00 AM'

The constant becomes a date type at compile time because of the comparison the date expression, removing the time value. View the execution plan an you will see date literal '2022-09-01' without the time.

In contrast, the second query uses a strongly-typed datetime variable. The value is unchanged at compile time because datetime has a higher data type precedence than date. The date column will be converted to datetime so you will not get equality for times other than midnight.

Dan Guzman0
Related