This error is generated when trying to create a table with a computed column from a Date
field.
Operand type clash: int is incompatible with date
If I comment out the computed column it works.
[DateInActive][Date] NULL, -- Date origination
[IsValid] AS (IsNull(DateInActive, 1)) -- Failure here
The thought on IsValid
is to simply return a bit
of on|off for this field to signify that no in-active date has been reported and if so, it is valid.
The column will ultimately be passed on to JSON in a For JSON
output as a is-a
field for usage outside SQL.
Full SQL
CREATE TABLE [history].[PhoneBook](
[PhoneBookId] [int] IDENTITY(1,1) NOT NULL,
[PersonId] [int] NOT NULL,
[PhoneId] [int] NOT NULL,
[DateActive][Date] NOT NULL,
[DateInActive][Date] NULL,
[IsValid] AS (IsNull(DateInActive, 1))
CONSTRAINT [PK_PhoneBook_1] PRIMARY KEY CLUSTERED
(
[PersonId] ASC,
[PhoneId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]