Why is casting from DATE to VARCHAR not deterministic?

Question

When I tried to add a calculated column to a SQL Server table, I found that an excellent lead of a column of type DATE directly in VARCHAR is considered non-deterministic. However, if I pulled out the individual parts of the date and produced them individually, then everything will be fine. I cannot come up with a reasonable explanation of why casting directly from DATE to VARCHAR would be non-deterministic. Does anyone have an explanation?

Ref.

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,

Yang

Answer 1

The string (varchar) representation of the date depends on your "locale" setting (for example, dates in the UK are often presented differently than in the US).

In the above example, your first CAST () explicitly defines the varchar format, and the second causes the database to check 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 CAST () with one locale parameter, change the locale, and then run MOST CAST (), and you get a different result. This is a definition of non-deterministic behavior.