A computed column is a virtual column that is not physically stored in the table, rather, it can use data from other columns to derive a value.


In the below query examples we add a computed column named ExpirationDate to the IDCARD table. It uses the equivalent SQL or Oracle dateadd function to add 3 years to the issue date. Issue date is populated when the operator clicks PRINT. We also use the equivalent SQL or Oracle function to format the raw datetime value to the preferred format.


NOTE: A computed column cannot be edited from IDMS. For this reason the field status must be set to READ-ONLY.


Workflow

  1. Add the computed column to the database
  2. Map the computed column to IDMS -  Add a IDMS User Field 
  3. Add it to card design



Microsoft SQL Server

Use SQL Management Studio to add a new computed column to a table

  1. Connect to the Database Engine.

  2. From the Standard bar, click New Query.

  3. Copy and paste the appropriate script into the query window and then click Execute.


To calculate a 3 year ExpirationDate in the format of MM/DD/YYYY

ALTER TABLE dbo.IDCARD
ADD ExpirationDate AS CONVERT(varchar,DATEADD(year, 3, ISSUE_DATE),101)
GO

An issue date of 6/5/2019 returns '06/05/2022'



To calculate a person's 21st birthdate in the format MM/YYYY

ALTER TABLE IDCARD
ADD BDAY21 as format(dateadd(year,21,dob),'MM/yyyy')
GO


Oracle

To calculate a 3 year ExpirationDate in the format of MM/DD/YYYY

ALTER TABLE IDCARD
ADD ExpirationDate as (TO_CHAR(ADD_MONTHS(ISSUE_DATE,36),'mm/dd/yyyy'));

An issue date of 6/5/2019 returns '06/05/2022'



NOTE: If the data type of the ISSUE DATE column is VARCHAR2, use the following query

ALTER TABLE IDCARD
ADD ExpirationDate as (to_char(ADD_MONTHS(to_date(ISSUED,'mm/dd/yyyy'),120),'mm/dd/yyyy'));