SQL Column AutoFill

Question

SQL Column AutoFill

William Batte 21

I have a sql table called tktproc with the following column proc_date with data type of datetime.
How can i autofill column acct_period from proc_date in the following format YYYYMM?

Accepted answer

4 additional answers

Your answer

Answer 1

Viorel 122.5K

In case of ancient servers, check the next statement:

_alter table tktproc add acct_period as (cast(YEAR(proc_date) * 100 + MONTH(proc_date) as varchar(max)))_

Show the error messages.

Answer 2

Viorel 122.5K

Try executing this statement:

alter table tktproc add acct_period as (FORMAT(proc_date, 'yyyyMM'))

Answer 3

William Batte 21

I'm having trouble getting this to run on SQL 2008R2

Answer 4

William Batte 21

That worked, thanks. Going forward how do I change the query to update new records.

Viorel 122.5K Reputation points

2021-05-21T18:24:47.54+00:00

If you use the above computed column, then the value of acct_period will be updated automatically after inserting new rows or modifying the proc_date values.
William Batte 21 Reputation points

2021-05-24T15:06:49.83+00:00

I'm new to sql and do on under stand your reply:

If you use the above computed column, then the value of acct_period will be updated automatically after inserting new rows or modifying the proc_date values.
Viorel 122.5K Reputation points

2021-05-24T16:44:11.44+00:00

This means that the acct_period column is a special one that will be managed by the system. You do not have to insert or update it. When you execute a query like SELECT or watch the data in Management Studio, its value is calculated and shown automatically based on current values of proc_date from the same rows. The formula is simple; it simply shows the year and month in YYYYMM format.
William Batte 21 Reputation points

2021-05-24T17:42:11.807+00:00

ok, I understand. Thanks
EchoLiu-MSFT 14,621 Reputation points

2021-05-25T01:10:47.81+00:00

As far as this forum is concerned, people generally accept helpful responses as answers. I suggest you choose Viorel-1 or my reply as the answer, if you think they are useful. Instead of accepting your question as an answer.

Regards
Echo

Answer 5

EchoLiu-MSFT 14,621

Hi @William Batte ,

Welcome to the microsoft TSQL Q&A forum!

The FORMAT function has been provided since the 2012 version, so it does not work in sql server 2008 R2. In earlier versions, use this:

ALTER TABLE tktproc ADD acct_period as (REPLACE(LEFT(proc_date,CHARINDEX('-',proc_date)+2),'-',''))

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards
Echo

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Jeffrey Williams 1,896 Reputation points

2021-05-24T16:15:54.913+00:00
A much simpler version - and better than FORMAT (which has known performance issues) would be to use convert:

ALTER TABLE tktproc ADD acct_period as (CONVERT(char(6), proc_date, 112));

Share via

SQL Column AutoFill

4 additional answers

Your answer