set nocount on drop table #d go
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
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 |