SQL: Add only a year value in a date column

Question

I want a table with the name of an employee and the year of his birth. ONLY THE YEAR IN DATE FORMAT:

CREATE TABLE EMPLOYEE(
  Name VARCHAR2(50) NOT NULL,
  Year_Birth DATE NOT NULL,
  PRIMARY KEY (Name)
);

I want to do this:

INSERT INTO EMPLOYEE(Name, Year_Birth)
VALUES ('John Smith', 1985);

But it doesn't work (cause i'm passing a number value to a date column). I also tried this:

INSERT INTO EMPLOYEE(Name, Year_Birth)
VALUES ('John Smith', to_date('1972','YYYY') )

If i try this one i will get this: ORA-02290: check constraint (PROJECTNAME.SYS_C0066777818) violated

Year_Birth must be a date column. Is there any way to achieve this?

Answer 1

I think birth year should be an INT, like this:

CREATE TABLE EMPLOYEE(
  Name VARCHAR(50) NOT NULL,
  Year_Birth INT NOT NULL,
  PRIMARY KEY (Name)
);

Then this should work:

INSERT INTO EMPLOYEE(Name, Year_Birth)
VALUES ('John Smith', 1985);

Answer 2

You can create a column which is of data type int. This will then let you save the year of birth.

If the column must be of data type date then you could just save the date as the first of Jan with the relevant year (eg. for the year 2017 enter '20170101'). This will still allow you then perform date calculations on the data.

Answer 3

You can create a column which is of data type int. The insert statement will work.

insert into employee(Name, Year_Birth)
values ('John Smith', 1985);

Answer 4

I am assuming that your mysterious check constraint SYS_C0066777818 is enforcing a rule that year_birth must be 1st January.

to_date('1972','YYYY') does not give the 1st January 1972 as you might expect, it gives the first of the current month, in 1972. Who knows why, but that's the way it works. If you want 1st January 1972 then you will have to specify it explicitly, for example:

insert into employee (name, year_birth)
values ('John Smith', date '1972-01-01');

or an equivalent to_date expression that includes at least the month, or trunc(somedatevariable,'YEAR').