function in a constraint

I want to make sure that only month end dates make it into the table. I coul
d
put if conditions in my insert/update stored procs or I could do that with a
constraint.
Is it possible to put a constraint in table that checks for certain
condition using user defined functions?
for example:
CREATE TABLE t1
(
c1 int,
mydate datetime
CONSTRAINT myconstraint month_end_check_function(mydate)
)
Where month_end_check_function is a function that returns true if c2 was
month end date such as 7/31/05 and would return false if c2 was 7/3/05.
TIA...sqlster,
SQL Server does support functions within a check constraint.
HTH
Jerry
"sqlster" <nospam@.nospam.com> wrote in message
news:48C643BE-FA2E-44C3-88DE-2C6596CDEAD1@.microsoft.com...
>I want to make sure that only month end dates make it into the table. I
>could
> put if conditions in my insert/update stored procs or I could do that with
> a
> constraint.
> Is it possible to put a constraint in table that checks for certain
> condition using user defined functions?
> for example:
> CREATE TABLE t1
> (
> c1 int,
> mydate datetime
> CONSTRAINT myconstraint month_end_check_function(mydate)
> )
> Where month_end_check_function is a function that returns true if c2 was
> month end date such as 7/31/05 and would return false if c2 was 7/3/05.
> TIA...
>|||On Fri, 7 Oct 2005 08:45:01 -0700, sqlster wrote:

>I want to make sure that only month end dates make it into the table. I cou
ld
>put if conditions in my insert/update stored procs or I could do that with
a
>constraint.
>Is it possible to put a constraint in table that checks for certain
>condition using user defined functions?
>for example:
>CREATE TABLE t1
>(
>c1 int,
>mydate datetime
> CONSTRAINT myconstraint month_end_check_function(mydate)
> )
>Where month_end_check_function is a function that returns true if c2 was
>month end date such as 7/31/05 and would return false if c2 was 7/3/05.
>TIA...
>
Hi sqlster,
Though Jerry is right - functions are permitted in a CHECK constraint -,
you don't need one here:
CREATE TABLE T1
(c1 int NOT NULL PRIMARY KEY,
mydate datetime,
CONSTRAINT (MONTH(mydate) <> MONTH(DATEADD(day, 1, mydate)))
)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Nice job Hugo - thinkin outside the box!
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:8vvdk159t9sefnbp02hn1rgsevlhqhdtu8@.
4ax.com...
> On Fri, 7 Oct 2005 08:45:01 -0700, sqlster wrote:
>
> Hi sqlster,
> Though Jerry is right - functions are permitted in a CHECK constraint -,
> you don't need one here:
> CREATE TABLE T1
> (c1 int NOT NULL PRIMARY KEY,
> mydate datetime,
> CONSTRAINT (MONTH(mydate) <> MONTH(DATEADD(day, 1, mydate)))
> )
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||Excellent (and creative) solution!
Gert-Jan
Hugo Kornelis wrote:
> Hi sqlster,
> Though Jerry is right - functions are permitted in a CHECK constraint -,
> you don't need one here:
> CREATE TABLE T1
> (c1 int NOT NULL PRIMARY KEY,
> mydate datetime,
> CONSTRAINT (MONTH(mydate) <> MONTH(DATEADD(day, 1, mydate)))
> )
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)