Should a year and month be stored as separate fields or as a date?

We have a table with calculated data that groups sales by product, year and month, to provide fast querying for statistics.

My colleague argues that the year and month should be two separate fields, because a day is meaningless.

I want it as a date field, because using two separate fields leads to awkward code like this

var lastTwelveMonths = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1).AddMonths(-11);
var result = data.Where(item =>
    (item.Year > lastTwelveMonths.Year
    || (item.Year == lastTwelveMonths.Year && item.Month >= lastTwelveMonths.Month));

instead of this

var lastTwelveMonths = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1).AddMonths(-11);
var result = data.Where(item => item.YearAndMonthAsDate >= lastTwelveMonths);

I understand his argument, but the code is harder to read and there's a higher chance on bugs when doing date/time calculations without using date/time objects. I also can't cast it to a DateTime in the query because I'm using LINQ to Entities.

Which method is the better one?

In general it really depends on the use cases of your data. If you need to perform queries using just year or just month then you reduce complexity in the code and queries by having separate fields.

However, if you aren't bounding by just a year or just a month then you would want to use a date field.

Professionally I use date or string fields (YYYYMMDD) when representing dates and times because the speedup you get from single year/month fields is small with todays databases (assuming things are correctly indexed).

StackExchange Code Review Q#40356, answer score: 7