How to store date and compare it in sql server?

Question

basically, i need to make database for plants and their harvest dates. For example, table with all the plants.

CREATE TABLE plant
    (
      plant_name NVARCHAR(20) NOT NULL
                              PRIMARY KEY ,
      best_to_harvest_day INT NOT NULL ,
      best_to_harvest_month NVARCHAR(15)
    )

Example for plant entry: Rose 16 December And another table called harvests Where are multiple harvested plants and dates when they were harvested.

CREATE TABLE harvests
(
    plant_name nvarchar(20) NOT NULL FOREIGN KEY REFERENCES plant(plant_name),
    amount int NOT NULL,
    havested_day int NOT NULL,
    harvested_month nvarchar(15),
    harvested year int NOT NULL
)

And this method does work, because i can make a sql query to compare which plants are harvested at their best time etc. But isnt there a tidy way? something like this: (using the date)

CREATE TABLE plant
(
  plant_name NVARCHAR(20) NOT NULL
                          PRIMARY KEY ,
  best_to_harvest DATE --But here should only be day and month, not year.
)

CREATE TABLE harvests
(
  plant_name NVARCHAR(20) NOT NULL
                          FOREIGN KEY REFERENCES plant ( plant_name ) ,
  amount INT NOT NULL ,
  harvested DATE --But here i need full date year,day,month
)

Bottom line is that i need to compare them. Okay, i think i can use EXTRACT(unit FROM date) and then compare them but the question still stands, how to make plant table date not to consist of year?

Answer 1

First, store the date parts as numbers and check their values. This isn't perfect, but probably good enough:

CREATE TABLE plant (
    plant_id int not null PRIMARY KEY,
    plant_name nvarchar(20) NOT NULL UNIQUE,
    best_to_harvest_day int NOT NULL,
    best_to_harvest_month int not NULL,
    check (best_to_harvest_day between 1 and 31),
    check (best_to_harvest_month between 1 and 12)
);

Note the inclusion of an integer identity primary key. This is recommended, because integers are more efficient for foreign key references.

Then use date for the harvest:

CREATE TABLE harvests (
    harvest_id int not null primary key,
    plant_id int NOT NULL FOREIGN KEY REFERENCES plant(plant_id),
    amount int NOT NULL,
    harvested date --But here i need full date year,day,month
);

And you can do:

select h.*,
       (case when p.best_to_harvest_day = day(h.harvest_day) and
                  p.best_to_harvest_month = month(h.harvest_month)
             then 'Y' else 'N'
        end)
from harvest h join
     plant p
     on h.plant_id = p.plant_id;