Build a string in a computed SQL column from specific characters from another column

Question

I do not know if this is possible, but I would like to fill in a column based on an adjacent column in a SQL Server table.

As below, we would like to dynamically determine the BillingDealerPeriodID by using the 6th and 7th and the 3rd and 4th characters from BillingDateTo.

I am not sure if it matters, but BillingDateTo is also a computed column based on another column BillingDateFrom, which is 7 days from BillingDateFrom.

The formula for the BillingDateTo computed column is:

(dateadd(day,(7),[BillingDateFrom]))

If the result had to be 2102 or 2103 instead of 0221 and 0321, that would be fine too.

BillingDateTo	BillingDealerPeriodID
2021-02-28 00:00:00.000	0221
2021-03-07 00:00:00.000	0321

Answer 1

This will produce YYMM, like 2102, 2103, etc.:

SELECT CONVERT(char(4), GETDATE(), 12);

So we can add that to the existing computed column formula. Since BillingDateTo is a computed column, you can't nest those; you have to repeat the computed column expression for BillingDateFrom or, instead of using multiple computed columns, just expose these additional calculations in a view.

ALTER TABLE dbo.whatever ADD BillingDealerPeriodID 
  AS (CONVERT(char(4), DATEADD(DAY, 7, BillingDateFrom), 12));

• Example db<>fiddle

If you really want MMYY instead of YYMM, you can do:

SELECT STUFF(CONVERT(char(10), GETDATE(), 110),3,6,'');

So as a computed column:

ALTER TABLE dbo.whatever ADD BillingDealerPeriodID 
  AS STUFF(CONVERT(char(10), DATEADD(DAY, 7, BillingDateFrom), 110),3,6,'');

• Example db<>fiddle

The documentation doesn't do a great job of explicitly stating that computed columns can't be nested, other than the following:

The expression can be a noncomputed column name

But the error message that results if you try is pretty explicit.

As an aside, I strongly recommend staying away from variations that use FORMAT() - while it's convenient, intuitive, and better matches capabilities in other more sophisticated languages, it is unacceptably slow (and also doesn't work in off-shoots like Azure SQL Edge).