How to limit an entry in a table to only the year of MS SQL Server 2005?

Question

SQL Server

At creation of the table it would be desirable to limit record in a column only to one year instead of all date. How can this be done? And we also need to make sure that this year is not larger than the current one.

Answer 1

There is no DATE type in SQL Server 2005, only DATETIME. I recommend upgrading to at least SQL Server 2008 R2, it has a YEAR field.
Checking for a value is done like this:

-- для поля с типом DATETIME
ALTER TABLE table_name ADD CONSTRAINT check_for_current_year CHECK (YEAR(date_field) <= YEAR(GETDATE()));

-- для поля с типом YEAR
ALTER TABLE table_name ADD CONSTRAINT check_for_current_year CHECK (year_field <= YEAR(GETDATE()));

Answer 2

Column Datatype: smallint
length: 4
CHECK for "no more" can be built following the example of Aleksey Ratnikov with datepart(year,getdate()). The DATEPART function has been available since MSSQL 2005 .