CODEWITHSUNDEEP
sql-serverdatetimeconstraintsddl
Daniel
Daniel

Reputation: 75

SQL Check datetime value constraint while creating a table

I would like to ask whether there is any possible way to do a check constraint on a datetime column, making sure that the column is either in the month of November or May.

Create Table TestDate (
.....
.....
date datetime not null default '14/11/2012'
...
...
constraint dateCheck check *date must be either November or May*

Thank you so much for your help!

Edit: Adding on to the question,

Would it be possible to set the default value for the date column to '14 November 2012' instead of '14/11/2012'? I understand that it is using date time data type, and can't be changed unless it is change to a string (char/varchar) data type?

Upvotes: 1

Views: 9632

Answers (4)

Paresh J
Paresh J

Reputation: 2419

Yes, you can add the constraint on month part of date column. Check the below example:

Create table TestTable
(
   from_date DAtetime

   constraint checkdates check (DATEPART(mm, from_date) = 11 OR DATEPART(mm, from_date) = 5)
);

Above script will restrict the insert into from_date column if datepart does not contains month November or May.

Yes, You can use Default value as '14 November 2012'. Check this version:

Create table TestTable
(

   from_date DAtetime default ('14 November 2012') 

   constraint checkdates check (DATEPART(mm, from_date) = 11 OR DATEPART(mm, from_date) = 5)
);

And, try this insert statement to check the default value: 

Insert into TestTable DEFAULT values

Upvotes: 2

Yazan Fahad Haddadein
Yazan Fahad Haddadein

Reputation: 533

You need to use Trigger (before insert, and before update) to handle your requirements. the following MSDN link might help you http://msdn.microsoft.com/en-us/library/ms189799.aspx

Upvotes: 0

Justin Cave
Justin Cave

Reputation: 231691

I would use the datepart function in a check constraint

create table testDate (
  dt datetime not null default '14/11/2012'
 );

alter table testDate 
  add constraint chk_month
    check( datepart( month, dt ) in (5,11));

You can see an example of this in this SQL Fiddle

Upvotes: 2

Mureinik
Mureinik

Reputation: 311528

You can use the month function to extract the month part of the date:

CREATE TABLE TestDate (
    date datetime not null default '14/11/2012'
    constraint dateCheck check (MONTH(date) IN (5, 11))
)

Upvotes: 3

Related Questions