Why is a cast from DATE to VARCHAR non-deterministic?
<p>In trying to add a computed column to a SQL Server table, I've found that casting a column with a DATE type directly to a VARCHAR is considered non-deterministic. However, If I pull out the individual parts of the date and cast them individually then everything is fine. I can't think of a reasonable explaination for why the cast directly from DATE to VARCHAR would be non-deterministic. Does anyone have an explanation?</p>

<p>Ex.</p>

<pre><code>create table [dbo].[junk_CCtest]
(
    PatientId bigint identity not null,
    EmployerId varchar(6) default 'F*Corp',
    EffDate date default getdate()
)
go
-- This works fine.
alter table dbo.junk_CCtest
    add Checksum1 as (hashbytes('sha2_256', EmployerId + '/' + cast(PatientId as varchar(10)) + cast(year(EffDate) as varchar(4)) + cast(month(EffDate) as varchar(2))  + cast(day(EffDate) as varchar(2)))) persisted;
go
-- This results in: "Computed column 'Checksum3' in table 'junk_CCtest' cannot be persisted because the column is non-deterministic."
alter table dbo.junk_CCtest
    add Checksum3 as (hashbytes('sha2_256', EmployerId + '/' + cast(PatientId as varchar(10)) + cast(EffDate as varchar(10)))) persisted;
go
</code></pre>

<p>Thanks,</p>

<p>Ian</p>