Another Check Constraint Error.

I have created a check constraint on a field called Date in a table called
StudentInfo. In the constraint expression I put Date = GETDATE() and
unchecked the "check existing date on creation" and the "enforce constraint
for replication" check boxes. I left the "Enforce constraint for INSERTs and
UPDATEs" checked. However, when I teachers tried to update this field this
morning, and enter today's date, they all got check constraint errors. Does
anybody have any idea what might be wrong? All I want is for the Date field
to only accept the current date when being updated.
Any help would be appreciated, I only have 9 teachers but, everyday at least
1 will get the date wrong.
Thanks.
KevinKevin,
GETDATE returns the current date and time accurate to 1/300 of a
second. It's very unlikely that anyone entering data by hand would
happen to enter precisely this value. If you want to get a date-only
for today into the table, instead of having the teacher enter it, why
not use a default on that column of
DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0) and leave that column out of the
column list and values the teacher enters?
SK
Kevin Sinclair wrote:
>I have created a check constraint on a field called Date in a table called
>StudentInfo. In the constraint expression I put Date = GETDATE() and
>unchecked the "check existing date on creation" and the "enforce constraint
>for replication" check boxes. I left the "Enforce constraint for INSERTs and
>UPDATEs" checked. However, when I teachers tried to update this field this
>morning, and enter today's date, they all got check constraint errors. Does
>anybody have any idea what might be wrong? All I want is for the Date field
>to only accept the current date when being updated.
>Any help would be appreciated, I only have 9 teachers but, everyday at least
>1 will get the date wrong.
>Thanks.
>Kevin
>
>|||I can't use a default because there is already a date in the field. What is
supposed to happen is the teachers update this field when they do their
daily attendance. However, at least 1 teacher every day, will input the
wrong date. When this database was in access I used a validation rule on
this field, I need a way to do the same thing in SQL Server 2000.
Thanks.
Kevin
"Steve Kass" <skass@.drew.edu> wrote in message
news:uAltir$7DHA.3112@.tk2msftngp13.phx.gbl...
> Kevin,
> GETDATE returns the current date and time accurate to 1/300 of a
> second. It's very unlikely that anyone entering data by hand would
> happen to enter precisely this value. If you want to get a date-only
> for today into the table, instead of having the teacher enter it, why
> not use a default on that column of
> DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0) and leave that column out of the
> column list and values the teacher enters?
> SK
> Kevin Sinclair wrote:
> >I have created a check constraint on a field called Date in a table
called
> >StudentInfo. In the constraint expression I put Date = GETDATE() and
> >unchecked the "check existing date on creation" and the "enforce
constraint
> >for replication" check boxes. I left the "Enforce constraint for INSERTs
and
> >UPDATEs" checked. However, when I teachers tried to update this field
this
> >morning, and enter today's date, they all got check constraint errors.
Does
> >anybody have any idea what might be wrong? All I want is for the Date
field
> >to only accept the current date when being updated.
> >
> >Any help would be appreciated, I only have 9 teachers but, everyday at
least
> >1 will get the date wrong.
> >
> >Thanks.
> >
> >Kevin
> >
> >
> >
> >
>|||Kevin,
You can check for the same value I was suggesting as a default:
create table Kevin (
...
dateCol datetime CHECK (dateCol = DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0))
...
Steve
Kevin Sinclair wrote:
>I can't use a default because there is already a date in the field. What is
>supposed to happen is the teachers update this field when they do their
>daily attendance. However, at least 1 teacher every day, will input the
>wrong date. When this database was in access I used a validation rule on
>this field, I need a way to do the same thing in SQL Server 2000.
>Thanks.
>Kevin
>
>"Steve Kass" <skass@.drew.edu> wrote in message
>news:uAltir$7DHA.3112@.tk2msftngp13.phx.gbl...
>
>>Kevin,
>> GETDATE returns the current date and time accurate to 1/300 of a
>>second. It's very unlikely that anyone entering data by hand would
>>happen to enter precisely this value. If you want to get a date-only
>>for today into the table, instead of having the teacher enter it, why
>>not use a default on that column of
>>DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0) and leave that column out of the
>>column list and values the teacher enters?
>>SK
>>Kevin Sinclair wrote:
>>
>>I have created a check constraint on a field called Date in a table
>>
>called
>
>>StudentInfo. In the constraint expression I put Date = GETDATE() and
>>unchecked the "check existing date on creation" and the "enforce
>>
>constraint
>
>>for replication" check boxes. I left the "Enforce constraint for INSERTs
>>
>and
>
>>UPDATEs" checked. However, when I teachers tried to update this field
>>
>this
>
>>morning, and enter today's date, they all got check constraint errors.
>>
>Does
>
>>anybody have any idea what might be wrong? All I want is for the Date
>>
>field
>
>>to only accept the current date when being updated.
>>Any help would be appreciated, I only have 9 teachers but, everyday at
>>
>least
>
>>1 will get the date wrong.
>>Thanks.
>>Kevin
>>
>>
>>
>
>|||Oops. Ignore my previous message. The check constraint will fail
tomorrow if you update the row! You may need to use a trigger. What
you could do is put in the default and execute the update with:
UPDATE Attendance SET
this = whatever,
that = whatever,
dateCol = DEFAULT
WHERE
keyCol = theRowBeingEdited
SK
Kevin Sinclair wrote:
>I can't use a default because there is already a date in the field. What is
>supposed to happen is the teachers update this field when they do their
>daily attendance. However, at least 1 teacher every day, will input the
>wrong date. When this database was in access I used a validation rule on
>this field, I need a way to do the same thing in SQL Server 2000.
>Thanks.
>Kevin
>
>"Steve Kass" <skass@.drew.edu> wrote in message
>news:uAltir$7DHA.3112@.tk2msftngp13.phx.gbl...
>
>>Kevin,
>> GETDATE returns the current date and time accurate to 1/300 of a
>>second. It's very unlikely that anyone entering data by hand would
>>happen to enter precisely this value. If you want to get a date-only
>>for today into the table, instead of having the teacher enter it, why
>>not use a default on that column of
>>DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0) and leave that column out of the
>>column list and values the teacher enters?
>>SK
>>Kevin Sinclair wrote:
>>
>>I have created a check constraint on a field called Date in a table
>>
>called
>
>>StudentInfo. In the constraint expression I put Date = GETDATE() and
>>unchecked the "check existing date on creation" and the "enforce
>>
>constraint
>
>>for replication" check boxes. I left the "Enforce constraint for INSERTs
>>
>and
>
>>UPDATEs" checked. However, when I teachers tried to update this field
>>
>this
>
>>morning, and enter today's date, they all got check constraint errors.
>>
>Does
>
>>anybody have any idea what might be wrong? All I want is for the Date
>>
>field
>
>>to only accept the current date when being updated.
>>Any help would be appreciated, I only have 9 teachers but, everyday at
>>
>least
>
>>1 will get the date wrong.
>>Thanks.
>>Kevin
>>
>>
>>
>
>