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
    
    select distinct SessionId from [User].Session s -- 1
     cross apply (select dateadd(day, -365, sysutcdatetime()) as MinCreatedUtc) d
     where d.MinCreatedUtc < s.CreatedUtc

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 SessionId                     -- 1
      from (select dateadd(day, -365, sysutcdatetime()) as MinCreatedUtc) d
     inner loop join [User].Session s    
        on s.CreatedUtc > d.MinCreatedUtc  
    option (recompile);

Try using Parameter/Hints (but N/A to View):

    declare
    	@minDate datetime2(7) = dateadd(day, -365, sysutcdatetime());
    	
    select distinct SessionId from [User].Session  -- 1.2M (adequate)
     where CreatedUtc > @minDate;
    
    select distinct SessionId from [User].Session  -- 2.96M (great)
     where CreatedUtc > @minDate
    option (recompile);
    
    select distinct SessionId from [User].Session  -- 1.2M (adequate)
     where CreatedUtc > @minDate
    option (optimize for unknown);


[![Estimate vs Actual][1]][1]


The statistics are up to date. 

    DBCC SHOW_STATISTICS('user.Session', 'IX_User_Session_CreatedUtc') with histogram;

The last few rows of the histogram (189 rows total) are shown:

[![enter image description here][2]][2]



  [1]: https://i.sstatic.net/pjTNV.jpg
  [2]: https://i.sstatic.net/9kVD5.jpg