How to insert just year and month into date field?

Question

I have a column called table_date which currently I am using now() to insert the current date (2011-02-23). I know I can manipulate this with sql/php to show me year and monthname. However, I want to know if it's possible to just insert into table_date the current date as year-month like this 2011-02? Thanks

Answer 1

A DATE field is always going to be a full date, and as far as I know, it is also always required to specify a full date.

It might be easiest to use 01, like 2011-02-01 for February 2011.

Obviously, you can format the output of a DATE field to your liking when querying it:

SELECT DATE_FORMAT(fieldname,"%Y-%m");

Answer 2

If you insert something like 2010-10 into a DATE column, mysql throws a warning and inserts 0000-00-00. If you do not want to specify a certain day, you can insert something like 2010-10-00. Pay attention when querying for "all entries in October 2010" since WHERE date >= '2010-10-01' will not return 2010-10-00.