/*
(Date2 must be between Date1 and Date3, if Date3 is not null),
otherwise (Date2 can be null or Date2 must be >= Date1). 
*/
set nocount on
drop table #d
go
-- One way to define the constraint
create table #d (date1 datetime, date2 datetime, date3 datetime,
check ((Date3 is null and (Date2 >= Date1 or Date2 is null)) or
                        (Date3 is not null and Date2 between Date1 and Date3))
)
go
insert #d values('1/1/99', '1/2/99', '1/3/99')
insert #d values('1/1/99', null, null)
insert #d values('1/1/99', '1/3/99', null)
insert #d values('1/1/99', '1/3/98', null)

select * from #d
go

drop table #d
go
-- Another way to define the same constraint eliminating the different checks for NULL
create table #d (date1 datetime, date2 datetime, date3 datetime,
check (isnull(date2, '9999-12-31 23:59:59.998') between date1 and
        isnull(date3, '9999-12-31 23:59:59.998'))
)
go
insert #d values('1/1/99', '1/2/99', '1/3/99')
insert #d values('1/1/99', null, null)
insert #d values('1/1/99', '1/3/99', null)
insert #d values('1/1/99', '1/3/98', null)

select * from #d
go

This page was last updated on May 01, 2006 04:28 PM.