CODEWITHSUNDEEP
sql-servercheck-constraintsssms-2014
HCM
HCM

Reputation: 19

SSMS Check Constraint Does not Allow Update of Table with valid data

I have the following in my table design from the Script Table as...Create To...New Query Window of SSMS:

CREATE TABLE [DirectMarketing].[Opportunity](
    [OpportunityID] [int] NOT NULL,
    [ProspectID] [int] NOT NULL,
    [DateRaised] [date] NOT NULL,
    [Likelihood] [tinyint] NOT NULL,
    [Rating] [char](1) NOT NULL,
    [EstimatedClosingDate] [date] NOT NULL,
    [EstimatedRevenue] [decimal](10, 2) NOT NULL
) ON [PRIMARY]
GO

SET ANSI_PADDING ON
GO

ALTER TABLE [DirectMarketing].[Opportunity] ADD  CONSTRAINT [DF_Opportunity_DateRaised]  DEFAULT (getdate()) FOR [DateRaised]
GO

ALTER TABLE [DirectMarketing].[Opportunity]  WITH NOCHECK ADD  CONSTRAINT [CK_Opportunity] CHECK  (([DateRaised]=getdate()))
GO

ALTER TABLE [DirectMarketing].[Opportunity] CHECK CONSTRAINT [CK_Opportunity]
GO

But when I try to test it by entering data into the table i keep getting an error saying no row was update. Kindly see the image below for data entered and subsequent error. What am I not doing right?

enter image description here

Upvotes: 0

Views: 497

Answers (1)

Dan Guzman
Dan Guzman

Reputation: 46415

The expression [DateRaised]=GETDATE() is comparing types of date and datetime. Because datetime has a higher precedence, the date value is converted to a datetime with a time of midnight. The comparison then returns false because of the non-midnight time.

The solution is to add a CAST:

ALTER TABLE [DirectMarketing].[Opportunity] 
    ADD  CONSTRAINT [CK_Opportunity] CHECK ([DateRaised]=CAST(getdate() AS date));

Upvotes: 0

Related Questions