locked
How can make this check constraint ? RRS feed

  • Question

  • Hi there

    I want make Check constraint for my table in design mode in SQL Server 2005 .(I dont want quary)

    Example wanna check "Day" is 'saturday' or 'sunday' or 'monday' ....

    thanks :)

     

    • Changed type Banafsheh Monday, March 29, 2010 12:42 PM
    Monday, March 29, 2010 12:29 PM

Answers

  • You should be able to write:

    (datename(weekday,[Day])='Saturday' OR datename(weekday,[Day])='Sunday')

    Assuming [Day] is the datetime/date column name.


    every day is a school day
    • Marked as answer by Banafsheh Thursday, April 15, 2010 10:40 AM
    Wednesday, March 31, 2010 8:33 AM

All replies

  • create table #days
    (dt date)
    
    -- day must be a saturday or sunday
    alter table #days
    add constraint ck_day_is_weekend check (datename(dw, dt) in ('Saturday', 'Sunday'))
    
    insert into #days select '20100326' -- friday fails
    insert into #days select '20100327' -- saturday is ok
    insert into #days select '20100328' -- sunday is ok
    
    You can add the following expression when adding a constraint using SSMS:
    (datename(weekday,[Dt])='Saturday' OR datename(weekday,[Dt])='Sunday')

     

     

     

    Monday, March 29, 2010 12:55 PM
  • Hi there

    I want make Check constraint for my table in design mode in SQL Server 2005 .(I dont want quary)

    Example wanna check "Day" is 'saturday' or 'sunday' or 'monday' ....

    thanks :)

     


    I mean when right click in blank area in table design and select Check constraint and opened check constraints window, at Expression area what must write ?

    like this :

     ([Day] like 'Saturday' OR [Day] like 'Sunday' OR [Day] like 'Monday' OR [Day] like 'tusday' OR [Day] like 'Wednesday' OR [Day] like 'Thursday' OR [Day] like 'Friday')

    Monday, March 29, 2010 2:21 PM
  • You should be able to write:

    (datename(weekday,[Day])='Saturday' OR datename(weekday,[Day])='Sunday')

    Assuming [Day] is the datetime/date column name.


    every day is a school day
    • Marked as answer by Banafsheh Thursday, April 15, 2010 10:40 AM
    Wednesday, March 31, 2010 8:33 AM
  • Day IN ('Sunday', 'Monday', 'tusday', 'Wednesday', 'Thursday', 'Friday')
    Wednesday, April 7, 2010 4:26 PM
    Answerer