January 3, 2008 at 6:34 am
I have a table emp where in empsal is a field in it. How can I check a condition where empsal can be null before 10th of a month but empsal can not be null on or after 10th.
January 3, 2008 at 8:06 am
create table emp (
empsal int null constraint emptest check (case when datepart(dd,getdate()) >= 10 THEN (CASE WHEN empsal IS NULL THEN 0 ELSE 1 END) ELSE 1 END = 1)
)
Basically a check constraint using a case statement to determine if its before or after the 10th and then another case statement checking if the value is null.
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
January 3, 2008 at 8:31 am
You can use the CHECK constraint either when you create the table or add it via an ALTER command. In the check constraint you can check the value of a column (if you place the constraint on a particular column) or use multiple columns of the same table (if you place the constraint at the table level).
In the check constraint you can test the value of the date versus the field you want to allow/disallow as NULL. See BOL for explanation of CHECK constraints.
Sample code below for table level constraint. You can move the constraint to the particular column if you want to check against the value of Getdate().
/* I have a table emp where in empsal is a field in it. How can I check a condition where empsal
can be null before 10th of a month but empsal can not be null on or after 10th. */
use pubs
go
if exists (select name from sysobjects where name = 'tempt' and type ='U')
drop table tempt
create table tempt (day datetime, today datetime)
-- check((datepart(dd,today)>11 and day is not null))
alter table tempt add Constraint checkdate check((datepart(dd,today)>10 and day is not null)
or (datepart(dd,today) <= 10))
go
insert into tempt -- this one should work
values ('12/27/2007', '12/01/2007')
select day,today from tempt
insert into tempt -- this one should work too (day is <= 10)
values (null, '12/10/2007')
Select day,today from tempt
insert into tempt -- this one will fail (day is > 10)
values (null, '12/11/2007')
Select day,today from tempt
Toni
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply