Store year and month only

[chandimak]

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.

[debasisdas]

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.

[jcarbillon]

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

[chandimak]

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.

[pclfw]

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.