Updating a date field returns error

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Updating a date field returns error

Author

Topic

aharvestofhealth
Yak Posting Veteran

52 Posts

Posted - 2009-04-29 : 11:30:49

I'm trying to run the following script in SQL Server Management Studio Express:

UPDATE AR_CUST
SET PROF_DAT_1 = CURRENT_TIMESTAMP
WHERE LOY_CARD_NO = '40127079000686'

When I execute, I get the following error:

Msg 547, Level 16, State 0, Line 1
The UPDATE statement conflicted with the CHECK constraint "CK_AR_CUST_PROF_DAT_1". The conflict occurred in database "TESTAHOH", table "dbo.AR_CUST", column 'PROF_DAT_1'. The statement has been terminated.

Any ideas?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-04-29 : 11:48:31

Looks like there is a constraint on that column which is preventing that value. Try this to see what the constaint is:

select CHECK_CLAUSE
from INFORMATION_SCHEMA.CHECK_CONSTRAINTS
where CONSTRAINT_NAME = 'CK_AR_CUST_PROF_DAT_1'

Be One with the Optimizer
TG

aharvestofhealth
Yak Posting Veteran

52 Posts

Posted - 2009-04-29 : 12:11:13

Ok, after running your statement, it returns the following:

([PROF_DAT_1] is null or [PROF_DAT_1] = [dbo].[fnDateOnly]([PROF_DAT_1]))

I noticed it shows fnDateOnly, and I'm going to assume this means that this field will only accept a date (which would be correct). However, I thought that using the CURRENT_TIMESTAMP would place the current date in that field.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-04-29 : 13:44:59

fnDaateOnly looks like a user defined function. Go ahead and run this:
select dbo.fnDateOnly(current_timestamp), current_timestamp

Once you see the output you should be able to see what the problem is. It may be that one returns DateTime and the other return SmallDateTime.

Be One with the Optimizer
TG

aharvestofhealth
Yak Posting Veteran

52 Posts

Posted - 2009-04-29 : 14:30:46

Your statement returned the two results below:

"2009-04-29 00:00:00.000" and "2009-04-29 14:24:03.093"

So what do I need to change it to?

quote:
Originally posted by TG

fnDaateOnly looks like a user defined function. Go ahead and run this:
select dbo.fnDateOnly(current_timestamp), current_timestamp

Once you see the output you should be able to see what the problem is. It may be that one returns DateTime and the other return SmallDateTime.

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-04-29 : 14:44:58

So your check constraint wants to make sure that you don't have the time component as part of the date. Assuming that is valid then you should change your update value to this:

dateadd(day, datediff(day, 0, getdate()), 0)

EDIT:
for that matter this would work too:

dbo.fnDateOnly(current_timestamp)

Be One with the Optimizer
TG

aharvestofhealth
Yak Posting Veteran

52 Posts

Posted - 2009-04-29 : 14:56:10

Wow, works perfect. So my script now looks like this:

UPDATE AR_CUST
SET PROF_DAT_1 = dateadd(day, datediff(day, 0, getdate()), 0)
WHERE LOY_CARD_NO = '40127079000686'

---------------------------------------------------------------------
My next step was to change the number 40127079000686 to NULL (so I could set the date on all of them):

UPDATE AR_CUST
SET PROF_DAT_1 = dateadd(day, datediff(day, 0, getdate()), 0)
WHERE LOY_CARD_NO = NULL

However, it only returns "(0 row(s) affected)"

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-04-29 : 15:25:30

Do you want to set PROF_DAT_1 to a date or LOY_CARD_NO to NULL or both?

aharvestofhealth
Yak Posting Veteran

52 Posts

Posted - 2009-04-29 : 15:33:46

quote:
Originally posted by Lamprey

Do you want to set PROF_DAT_1 to a date or LOY_CARD_NO to NULL or both?

I want to set the PROF_DAT_1 to a date where LOY_CARD_NO is null

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-04-29 : 15:41:46

quote:
Originally posted by aharvestofhealth

UPDATE AR_CUST
SET PROF_DAT_1 = dateadd(day, datediff(day, 0, getdate()), 0)
WHERE LOY_CARD_NO = IS NULL

However, it only returns "(0 row(s) affected)"

aharvestofhealth
Yak Posting Veteran

52 Posts

Posted - 2009-04-29 : 16:10:49

Ahhh, IS NULL! Thanks, it works now.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources