sql server - Why is a cast from DATE to VARCHAR non-deterministic?

Question

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?

Ex.

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

Thanks,

Ian

Answer 1

The string (varchar) representation of a date depends on your "locale" settings (e.g. dates in UK are often represented differently than in the US).

In your example above, your first CAST() explicitly specifies the format of the varchar, but the second one forces the database to examine its locale settings to determine how to format the varchar result.

The simple fact that the conversion depends on something external to the CAST() function makes it non-deterministic.

In other words, you run the CAST() with one locale setting, change the locale then run the SAME CAST() again, and you get a different result. This is the definition of non-deterministic behavior.