Computed Column Operand type clash on Date Column When Checking For Null

by Ī©megaMan   Last Updated October 19, 2019 20:06 PM - source

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]


Answers 1


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]
Dan Guzman
Dan Guzman
October 19, 2019 20:20 PM

Related Questions


When are computed columns computed?

Updated April 27, 2016 09:02 AM

Referencing another table from a computed column

Updated November 27, 2017 22:06 PM

Add computed column with "IN" syntax

Updated January 09, 2018 19:06 PM


Computed field when NULL

Updated December 27, 2016 08:02 AM