I have a monthly fee collecting table. I need to store year and month only for that purpose. For example just like to store, John has done the payment for 2010 July. I thought of using a DATE field for this. I can store the year and month with valid values and denote date with 0. (2011-07-00)

I need to know whether there is a way to insert the year and month values only without setting the date value to 0 manually. Further when retrieving the values there are functions to get YEAR() and MONTH(), MONTHNAME() separately. Is there a way to retrieve these two only together like it can be retrieved the year and week together by YEARWEEK().

Any clarifications highly appreciated.

What is the problem if you store the complete date, you can always display only the month and year part for any reporting. You can't store 00 as a date value.

Another option is to store the month and year values as string not dated.

debasisdas is right,you can filter a date column by day,month or year.. splitting the date column into month and year only is not a good practice unless you have something in mind. let us know

Thank you for your suggestions, both of you.

Why I wanted to store just year and month only without all year, month and date was because of a user can make the monthly fee even later (in another month). So, we can't store that particular date. I actually store the payment day separately.

I don't wish to store year, month as strings because I need to use date functions to manipulate them. (eg : year(), month())

When I check with the insertions for a date field it allows entering year and month and a date as a '0' value. This allows date field just to store year and month only without having some irrelevant data, the day.

Eg : insert into year_month(the_month) values('2011-08-00'); - this works
insert into year_month(the_month) values('2011-08'); - this gives a warning and inserts all 0s to the field.('0000-00-00')

So, according to my requirement, I wish to use the date field to store my data with 0s for the date section. I can retrieve year and month separately when manipulating and achieve my purpose by combining these 2 in the application side.

I value your ideas. I actually wanted to know whether there was a more convenient way to achieve this.

Write a function that takes a date value, (use whatever input format that you want) as input and gives your YYYYMM as output. Output the data in YYYYMM format. Not only will it be search-able, it was also be sortable. Use that function where ever you need to convert a data.