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

Question

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?

Answer 1

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).

Answer 2

If you're looking for an argument by authority, consider Microsoft's aversion to GetVersionEx and in particular misuse of the OSVERSIONINFOEX structure in calculations that look suprisingly similar. By analogy to the reasons they shim the results from GetVersionEx for program compatibility, it's apparently far to easy to write code that means to say "After February 2010" as month > 2 && year > 2010, when the correct code is the more complicated year > 2010 || (year == 2010 && month > 2) as your example shows.

Per a lack of understanding of the limitations of LINQ to entities, I thought your example was needlessly complex, and exhibited a flawed argument. After all, if you can compare to a DateTime that you've just constructed, and can do the complex logic, why not just encapsulate it back into the DateTime class?

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

However as you clarified in your comments below, L2E does not support creating a DateTime from separate columns inside a Where expression. Instead it's mapping the comparison DateTime lastTwelveMonths date into database queries, and is unable to map from my counterexample to the same queries as generated in your first example.