SQL Server Convert datetime to date without null

Question

I have a stored procedure like so:

select 
    TransactionDate 
from 
    (select 
         cast(TransactionDate as Date) as TransactionDate
     from RetailTransaction) t

However this makes the TransactionDate column of the outer-select nullable, while RetailTransaction.TransactionDate column is not null.

RetailTransaction.TransactionDate definition / design:

Inner Select:

Outer Select:

Even after adding isnull or coalesce SQL Server / SSMS still shows that the outer selects TransactionDate column is still nullable.

select 
    TransactionDate 
from 
    (select 
         isnull(cast(TransactionDate as Date), getdate()) as TransactionDate
     from 
         RetailTransaction) t

How do I make the TransactionDate column non nullable?

Note that the database is on Azure with compatibility level 100 (SQL Server 2008).

EDIT:

Adding an isnull on the outer select still make the column nullable to an outer-nested-query:

user_8117146 · Answer

Yes the reason it is showing Nullable for not nullable column is because you are making your outer select as computed based on isnull condition and others.

In this table Email is not nullable column and now if I give Isnull condition in inner query it will be like this.

Now if I don't give any condition it will be like this.

You can refer to this link as well for detailed explanation.

https://dba.stackexchange.com/questions/114260/why-is-a-not-null-computed-column-considered-nullable-in-a-view

user_3094533 · Answer

After reading your comment I did some digging and testing, and even though I couldn't find official documentation about it, you are correct and the casting to date makes the value nullable, even if the datetime is not nullable. I've even tried a different method by using datetimefromparts to create a date data type but that also changes the nullablility of the result.
So the way I see it, you have two options:

The first option is to add a persisted computed column to the table that will hold the date value of the transaction date. It has to be persisted to be non nullable:

ALTER TABLE RetailTransaction
    ADD TransactionDateOnly AS CAST(TransactionDate As Date) PERSISTED NOT NULL;

and then your query looks like this:

SELECT TransactionDateOnly as TransactionDate
FROM RetailTransaction

The other option would be to declare a table variable with a non-nullable date column, select the cast results into it, and then select from the table variable:

DECLARE @Target AS TABLE
(
    TransactionDate Date NOT NULL
)
INSERT INTO @Target(TransactionDate)
SELECT CAST(TransactionDate as Date) 
FROM RetailTransaction) t

And then your select looks like this:

SELECT TransactionDate 
FROM  @Target

Both ways will get you a non-nullable date as a result, but I think the persisted computed column option should probably yield better performance on the select, because it doesn't require that extra insert...select.

user_694659 · Answer

The trick is to put the ISNULL on the outer query and add an alias so that you don't lose the name

select 
    isnull(TransactionDate, GETDATE()) as TransactionDate
from ( 
    select 
        Cast(TransactionDate as Date) as TransactionDate
    from RetailTransaction
) t

That way SQL will realise that the field cannot be null. This should nicely inform any ORM that this field should not be mapped to a nullable type.

SQL Server Convert datetime to date without null

3 Answers