Persisted Computed Columns In T-SQL – Non-deterministic Error

I don’t often use computed columns in SQL Server, but had a situation at work where we needed to add one.
Here’s a simplified version of what we did. This isn’t a normalized table, but this is just an example to show an issue I had.

Here’s an example table:

DROP TABLE IF EXISTS dbo.PurchaseLog;

CREATE TABLE dbo.PurchaseLog (
	ID INT NOT NULL IDENTITY(1,1),
	CustomerName VARCHAR(100) NOT NULL,
	ProductName VARCHAR(100) NOT NULL,
	PurchaseDate DATE NOT NULL
);

INSERT INTO dbo.PurchaseLog(CustomerName, ProductName, 
    PurchaseDate)
VALUES
	('John Doe', 'Saw', '2026-06-24'),
	('Jane Smith', 'Ladder', '2026-06-25'),
	('John Doe', 'Nails', '2026-06-26'),
	('John Doe', 'Hammer', '2026-06-26'),
	('Mary Jones', 'Rake', '2026-06-27');

Let’s say that we want to add a code with each record, where we’ll combine the Purchase Date with the Identity ID.
We’ll create this PurchaseCode column as a computed column, concatenating the Purchase Date and the ID. We’ll also persist the value, so that we could index it, if we wanted to.

ALTER TABLE dbo.PurchaseLog ADD
   PurchaseCode AS CONCAT(CAST(PurchaseDate AS VARCHAR(10)), 
   ':', RIGHT('0000' + CAST(ID AS VARCHAR(10)), 5)) PERSISTED;

But running this statement gives an error:
Msg 4936, Level 16, State 1, Line 21
Computed column ‘PurchaseCode’ in table ‘PurchaseLog’ cannot be persisted because the column is non-deterministic.

So this error message surprised me. I didn’t see anything non-deterministic about the statement. The date I’m using is a static value, it’s not like we’re using GETDATE() or some non-deterministic function.
After a little research, it turns out that the date is the problem, or more precisely, the formatting of the date. Since I’m casting the date to a varchar, the format of the date is going to depend on my settings for displaying dates. Since that format can vary between installations, we could possibly end up with different values.

The answer was to specify a specific format for that date. In this case, I used CONVERT along with the 112 format code, which return the date as YYYYMMDD.

ALTER TABLE dbo.PurchaseLog ADD
	PurchaseCode AS CONCAT(CONVERT(VARCHAR(8), PurchaseDate, 112), ':', RIGHT('0000' + CAST(ID AS VARCHAR(10)), 5)) PERSISTED;

This statement worked, giving me these values:

At first, I tried using FORMAT(PurchaseDate, ‘yyyyMMdd’) for the date, but I got the same non-deterministic error. It turns out any use of the FORMAT function is considered non-deterministic, since it’s possible to just use a code like ‘d’ to format the entire date, which then relies on the language/date settings.