Hey.
I would recommend using two DATE fields, one for the starting year and one for the end year. It is better than using a single INT, from a data-integrity point of view, as MySQL will actually be able to treat the dates as dates, rather than numbers, and putting two dates into a single field violates the first rule of database normalization.
That aside, you should always store your data inside your database in a neutral format. Storing two dates in a "financial" format is not neutral, and might even require your front-end to parse the data out of the database - to separate the two dates to use them by themselves - which is just horrific.
If you are concerned about storage space requirements, there is hardly any need. Each DATE field takes only 3 bytes, while a single INT takes 8. You could probably get away with using a MEDIUMINT, which takes 3 bytes, but saving 3 bytes of space is hardly worth sacrificing the integrity of the data and the benefits you get from use the DATE type.
And if you want your query to return the data as '2008-09', you could just have it construct it out of the two dates:
- SELECT
-
CONCAT(
-
YEAR(`first_date`), '-',
-
DATE_FORMAT(`second_year`, '%y')
-
) AS 'financial_date'
-
FROM `my_table`;
Although, using the front-end application to do such formatting is probably better, all things considered.
232
