Sql-server – Computed Column Operand type clash on Date Column When Checking For Null

computed-columnsql servert-sqltable

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]

Best Answer

According to the ISNULL documentation the returned data type is the same type as the check expression, which is data type date in this case. The ISNULL expression fails because the integer literal 1 cannot be implicitly converted to date.

To return a bit indicating whether or not the date column is NULL, you could instead use a CASE expression:

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 CASE WHEN DateInActive IS NULL THEN CAST(0 AS BIT) ELSE CAST(1 AS bit) END
 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]
Related Question