How to improve estimate of 1 row in a View constrained by DateAdd() against an index

Using Microsoft SQL Server 2012 (SP3) (KB3072779) - 11.0.6020.0 (X64).

Given a table and index:

create table [User].[Session] 
(
  SessionId int identity(1, 1) not null primary key
  CreatedUtc datetime2(7) not null default sysutcdatetime())
)

create nonclustered index [IX_User_Session_CreatedUtc]
on [User].[Session]([CreatedUtc]) include (SessionId)

Actual Rows for each of the following queries is 3.1M, the estimated rows are shown as comments.

When these queries feed another query in a View, the optimizer chooses a loop join because of the 1 row estimates. How to improve the estimate at this ground level to avoid overriding the parent query join hint or resorting to an SP?

Using a hardcoded date works great:

select distinct SessionId from [User].Session -- 2.9M (great)
  where CreatedUtc > '04/08/2015'  -- but hardcoded

These equivalent queries are view compatible but all estimate 1 row:

select distinct SessionId from [User].Session -- 1
 where CreatedUtc > dateadd(day, -365, sysutcdatetime())         

select distinct SessionId from [User].Session  -- 1
 where dateadd(day, 365, CreatedUtc) > sysutcdatetime();          

select distinct SessionId from [User].Session s  -- 1
 inner loop join  (select dateadd(day, -365, sysutcdatetime()) as MinCreatedUtc) d
    on d.MinCreatedUtc < s.CreatedUtc    
    -- (also tried reversing join order, not shown, no change)

select distinct SessionId from [User].Session s -- 1
 cross apply (select dateadd(day, -365, sysutcdatetime()) as MinCreatedUtc) d
 where d.MinCreatedUtc < s.CreatedUtc
    -- (also tried reversing join order, not shown, no change)

Try some hints (but N/A to View):

```
select distinct SessionId from [User].Session -- 1
where CreatedUtc > dateadd(day, -365, sysutcdatetime())
option (recompile);

select distinct SessionId from [User].Session -- 1
where CreatedUtc > (select dateadd(day, -365, sysutcdatetime()))
option (recompile, optimize for unknown);

select distinct

A less comprehensive answer than Aaron's but the core issue is a cardinality estimation bug with DATEADD when using the datetime2 type:

Connect: Incorrect estimate when sysdatetime appear in a dateadd() expression

One workaround is to use GETUTCDATE (which returns datetime):

WHERE CreatedUtc > CONVERT(datetime2(7), DATEADD(DAY, -365, GETUTCDATE()))

Note the conversion to datetime2 must be outside the DATEADD to avoid the bug.

An incorrect cardinality estimation reproduces for me in all versions of SQL Server up to and including 2019 CU8 GDR (build 15.0.4083) when the 70 model cardinality estimator is used.

Aaron Bertrand has written an article about this for SQLPerformance.com:

• Performance Surprises and Assumptions : DATEADD()

StackExchange Database Administrators Q#134828, answer score: 7