How to specify year only as a datatype?

Question

I'm creating a table and want to store one field as date datatype. However I only want to store the year and not additional info (ie: month, day, or time.)

Here is what I have so far:

CREATE TABLE Project
(ProjectID int not null identity(1,1) unique
,PreviousYear1 date                         -- I need this format: (YYYY)
)

Any suggestions on how to store a year only date data-type? I'm working in ssms -2012. My aim is to accomplish this without later having to convert the datatype or alter it to only show year.

Answer 1

We can't, but you could use:

CREATE TABLE Project(
    ProjectID int not null identity(1,1) unique
    ,PreviousYear1 VARCHAR(4) -- To save the year only, INT it's better ;)
)

When you'll save a record just put a string with the year :)

INSERT INTO Project(PreviousYear1) VALUES ('1994'),('2015')

Answer 2

You should use an INT data type and put some check constraints on the table to enforce reasonable values, like no negative numbers and such. The Date data type is exactly that, a complete date.

Answer 3

CREATE TABLE Project
(ProjectID int not null identity(1,1) unique
,PreviousYear1 smallint   
 CONSTRAINT CHK_PreviousYear1_validity 
   CHECK (PreviousYear1 >= 1000 and PreviousYear1 <= 9999)                       
)

smallint goes up to 32,767
So you would use a update and insert trigger to limit to 1000 - 9999
Or more likely 1900 - 9999

Check constraint is a better answer
I borrowed from Roit, give that the check

Answer 4

The SQL below will show you how to use a check constraint to do this (update the year range as per your requirements):

CREATE TABLE Project
  (
    ProjectID int not null identity(1,1) unique,
    PreviousYear1 int 

    CONSTRAINT CHK_PreviousYear1_validity 

    CHECK (PreviousYear1 > 1900 and len(PreviousYear1) = 4)                     
  )