Using DateAdd with Negative value in constraint not working 1

[webuser]

I have either discovered a bug or I am doing something totally wrong, but when I try to subtract days off of a Date field using DateAdd in a constraint, SQL Enterprise Manager is saying the constarint is not valid. My code is very simple. The constraint that does not work is
[MyDateTime1] < DateAdd(d,-6,[MyDateTime2])

I was playing around with it and every value for the number seems to work from -1 on up, but anything below -1, i.e -2 and lower does not work. I thought at first it had to do with the dash character itself, but -1 works. I then tried weird workarounds like using
[MyDateTime1] < DateAdd(d,Sign(-1)*6,[MyDateTime2])
but that didn't work either.

Is this a bug or am I doing something wrong? If it is a bug, is there a workaround? I don't want to resort to Triggers if possible...

Thanks in advance!!!

 

[JamesLean]

This works fine for me:

IF OBJECT_ID('tempdb..#t') IS NOT NULL
	DROP TABLE #t

CREATE TABLE #t (
	date1 datetime,
	date2 datetime,
	CONSTRAINT ck_dates CHECK (date1 < DATEADD(dd, -6, date2))
)

--ok
INSERT #t VALUES ('20050706', '20050801')

--fails
INSERT #t VALUES ('20050706', '20050707')

SELECT * FROM #t

--James

 

[amrita418]

Do you already have data in the table? If yes, double check that they meet the constraint.

Regards,
AA

 

[webuser]

Amrita,

I do have data in the table, but that's not the problem. The constraint itself is not being 'verified'.

James,

Thanks. I guess I'll do it your way. But do you think
then that it's an Enterprise Manager problem? Did you try it in Enterprise Manager? I'm curious to know if it's me or if it's a real bug of some sort...

 

[vongrunt]

Lemme guess: both columns are of type smalldatetime?

EM attempts to validate constraint by evaluating expression with hard-coded value 1 - which implicitely converts into Jan 2nd 1900. The rest is obvious (overflow).

In other words: constraint is OK, don't always trust EM .

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[webuser]

You are correct. That's the explanation I wanted. Thanks!!!