In a database, would you use a date field or year and month fields if you only need year and month?

Question

I am setting up a table where I need the year and month. In MySQL I believe I have 2 options: (1) 2 fields: 1 for year, 1 for month or (2) a date field (the day would always be 1).

The 2 fields has the advantage of being some what faster (I think) because MySQL doesn't have to convert the value from a date to an integer although this is probably negligible. The date field has the advantage of "automatic" validation: someone can't get data into the db with the the month being 13 or the year being 1. With a date field you can also do date calculations more easily (ie, months between).

Which would you use? Or is there another you would use?

Answer 1

Use a date field. Since sql supports date fields natively, its easy to filter for specific dates by using the WHERE clause.

The 2 fields has the advantage of being some what faster [...]

Your SELECT query is not your bottleneck so you shouldn't worry about this. Readability and a pragmatic program is more important than a "perceived bottleneck".

Answer 2

I would use the date field even if you only need the year and month you don't lose anything by gathering all the data. As a standard practice i always gather all data when ever possible.