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

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

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

CodePudding user response：

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).

CodePudding user response：

You can use EXTRACT() and FORMAT(). For example:

select format(datepart(month, BillingDateTo), '0#')  
       format(datepart(year, BillingDateTo) - 2000, '0#')
from t

Result:

(No column name)
----------------
0221
0321

See running example at db<>fiddle.

CodePudding user response：

One can't create a computed column based on another computed column.

So you'll have to base it on a real column like BillingDateFrom

ALTER TABLE dbo.your_table
 ADD BillingDealerPeriodID
  AS (FORMAT(dateadd(day,7,[BillingDateFrom]), 'MMyy'));

Demo on db<>fiddle here