CODEWITHSUNDEEP
sqldate
JackyBoi
JackyBoi

Reputation: 2773

sql query accept only dates of two months

I am trying to create a table with a constraint where the date column should only accept any dates in the month of May or November. How should I write the constraint like?

Upvotes: 0

Views: 885

Answers (3)

Mladen Uzelac
Mladen Uzelac

Reputation: 1261

I concluded from your comments that you are using Management studio which is a part of SQL Server.
It uses the datepart() function. You can see more here Datepart documentation

So for case it would be:

create table tbl(
  date_col date 
  check(datepart(month, date_col) = 5 OR datepart(month, date_col) = 11)
);

Upvotes: 0

Asha
Asha

Reputation: 66

I may be misunderstanding your question. If you're just trying to retrieve values in may and nov, you can do this:

SELECT column1, column2, column3 
FROM yourTable 
WHERE MONTH(yourTimeStamp)=5 or MONTH(yourTimeStamp)=11

If you're trying to keep values from other months out of the DB, you should really do that in the software that interfaces with the DB.

I'm no sql guru, but in the rare circumstance where I'm trying to discourage foreign software from messing with a DB in any way other that what I'm allowing, I'll write stored proceedures that take care of the constraints and just publish those proceedures to the software providers.

Upvotes: 0

NP3
NP3

Reputation: 662

There are two ways to achieve this: Using CHECK CONSTRAINT on the column or a TRIGGER.

The CHECK Constraint could look like this:

create table tbl(
    date_col date 
    check( MONTH(date_col) = 5 OR MONTH(date_col) = 11)
)

You have not specified a particular database so treat this as a pseudo-code only.

Upvotes: 3

Related Questions