SQL server Date = @date but executionplan xml show it in inequality

Question

So I was trying to guess which missing index SQL Server is going to suggest based on a query I wrote. Please help me understand why this is not considered "equality" by execution plan XML? I am using SQL Server 2017.

I expected this to be considered "equality" and obviously made the wrong guess on the order of missing index creation syntax.

DB name - Stackoverflow2013

Query:

set statistics io on
declare   @year int = 2008
declare @date nvarchar(10)

while @year <= 2015
begin
    set @date =  cast (@year as nvarchar(6))+ cast('-05-27' as nvarchar(6))
    select
        title,
        cast (CreationDate as date)
    from
        posts
    where
        cast (CreationDate as date) = @date
        and title = 'Tool to diagonalize large matrices'

    set @year = @year + 1
end

Missing Index suggestion:

/*
create NONCLUSTERED INDEX [NonClusteredIndex_creationdate]
ON [dbo].[Posts] ([Title],[CreationDate])
*/

Execution Plan XML:

<MissingIndexes>
  <MissingIndexGroup Impact="99.9676">
    <MissingIndex Database="[StackOverflow2013]" Schema="[dbo]" Table="[Posts]">
      <ColumnGroup Usage="EQUALITY">
        <Column Name="[Title]" ColumnId="19" />
      </ColumnGroup>
      <ColumnGroup Usage="INEQUALITY">
        <Column Name="[CreationDate]" ColumnId="8" />
      </ColumnGroup>
    </MissingIndex>
  </MissingIndexGroup>
</MissingIndexes>

Answer 1

Assuming CreationDate is a datetime, you've got CreationDate wrapped in a transformation that eliminiates the time portion:

where
    cast (CreationDate as date) = @date

The query can only match on Title, and then must scan all the CreationDates for that Title for the whole day. There's special support for this particular pattern (explained here), and internally the expression is transformed into a range predicate on the CreationDate, thus the inequality.

Some caviats for this approach are here, and the query is probably better in the form:

where CreationDate >= @date
  and CreationDate < dateadd(day,1,@date)