CODEWITHSUNDEEP
sqldatabasesql-server-2008
user1789721
user1789721

Reputation: 87

How can you define a computed column like this?

I have an Equipment table in SQL Server 2008 like this:

CREATE TABLE [dbo].[Equipment](
[EquipmentID] [nchar](10) NOT NULL,
[EquipmentName] [nchar](50) NOT NULL,
[ProducedDate] [date] NOT NULL,
[WarrantyPeriod] [int] NOT NULL,
[SerialNumber] [nchar](20) NOT NULL,
[BrandID] [tinyint] NOT NULL,
CONSTRAINT [PK_Equipment] PRIMARY KEY CLUSTERED 
)

I want to have a computed column WarrantyStatus that will return either Unexpired or Expired when calculating, based on columns ProducedDate and WarrantyPeriod.

This is wrong, but it's what I want:

ALTER TABLE [dbo].[Equipment]
ADD [WarrantyStatus] AS IIF(DATEDIFF(MONTH, [ProducedDate], GETDATE()) < [WarrantyPeriod], "Unexpired", "Expired")

Upvotes: 0

Views: 93

Answers (1)

D&#39;Arcy Rittich
D&#39;Arcy Rittich

Reputation: 171381

Try:

ALTER TABLE [dbo].[Equipment]
ADD [WarrantyStatus] AS 
case when DATEDIFF(MONTH, [ProducedDate], GETDATE()) < [WarrantyPeriod]
then 'Unexpired'
else 'Expired'
end

Upvotes: 2

Related Questions