datatype with data like 2008-09

[kkshansid]

what datatype should i take for field financial year with data like 2008-09?
kindly suggest........ .........

[mwasif]

Use an INT field and store year and month as 200809.

[kkshansid]

Use an INT field and store year and month as 200809.

these are not year & month its financial year 2008-09(march 2008 to march 2009)

[Atli]

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.

[Atli]

Use an INT field and store year and month as 200809.

That is not a good idea, seeing as an INT field is 250% larger than a DATE field, which is specifically tailored to store such data.
You could use a MEDIUMINT, which is equal in size to a DATE field, but by doing that you lose all the benefits the DATE types get, and you compromise the integrity of the data. (Integer fields could just as well accept invalid dates, while a DATE field would not.)

You should always try to use the DATE types for dates.