Hi,

I was wondering how to do this.

I have a table with two columns in design view (start date, end date).

How do I set it within sql server (as constraint) or whatever that the
start date less than or equal to end date?

Thanks

HRUV

On 15 Jun 2004 14:07:05 -0700, Dhruv wrote:

>Hi,
>
>I was wondering how to do this.
>
>I have a table with two columns in design view (start date, end date).
>
>How do I set it within sql server (as constraint) or whatever that the
>start date less than or equal to end date?
>
>Thanks
>
>HRUV

Hi Dhruv,


Use a CHECK contraint:

CREATE TABLE MyTest (PK int not null primary key,
StartDate datetime not null,
EndDate datetime,
CONSTRAINT Date_CK CHECK (StartDate < EndDate))
(untested)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[posted and mailed, please reply in news]

Dhruv (dmalhotr2001@yahoo.com) writes:
> I was wondering how to do this.
>
> I have a table with two columns in design view (start date, end date).
>
> How do I set it within sql server (as constraint) or whatever that the
> start date less than or equal to end date?

CREATE TABLE hoppla (a int NOT NULL,
startdate datetime NOT NULL,
enddate datetime NULL,
CONSTRAINT pk_hoppla PRIMARY KEY (a),
CONSTRAINT ckt_dates CHECK (startdate <= endddate))

Note: this is a command to run in Query Analyzer. I guess that with
"design view" you mean something in Enterprise Manager, but I don't
use that, so I don't know you add a constraint this way.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Thanks a lot,

what about if the table was already created, can you update a table
with a constraint??





Erland Sommarskog <esquel@sommarskog.se> wrote in message news:<Xns950A1A45607Yazorman@127.0.0.1>...
> [posted and mailed, please reply in news]
>
> Dhruv (dmalhotr2001@yahoo.com) writes:
> > I was wondering how to do this.
> >
> > I have a table with two columns in design view (start date, end date).
> >
> > How do I set it within sql server (as constraint) or whatever that the
> > start date less than or equal to end date?
>
> CREATE TABLE hoppla (a int NOT NULL,
> startdate datetime NOT NULL,
> enddate datetime NULL,
> CONSTRAINT pk_hoppla PRIMARY KEY (a),
> CONSTRAINT ckt_dates CHECK (startdate <= endddate))
>
> Note: this is a command to run in Query Analyzer. I guess that with
> "design view" you mean something in Enterprise Manager, but I don't
> use that, so I don't know you add a constraint this way.

Dhruv (dmalhotr2001@yahoo.com) writes:
> what about if the table was already created, can you update a table
> with a constraint??

Yes, you can use the ALTER TABLE statement. Check Books Online for
details.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

dmalhotr2001@yahoo.com (Dhruv) wrote in message news:<b6d0b0b.0407071642.1ebc6104@posting.google.c om>...
> Thanks a lot,
>
> what about if the table was already created, can you update a table
> with a constraint??
>
>
>

alter table hoppla add constraint ckt_dates CHECK (startdate <= endddate)

Simon