Capture Date and Year from a single input Field

Databases
1

  1. I’m not sure where to post this… It’s a PHP/mySQL -related question.

  2. I may not be approaching this correctly…

Here’s what I’m trying to do. I want to be able to input a date (y,m,d). As part of this input, I want to capture the year in a separate field.

Why?

I use the date for reporting.

I use the year for filtering. I understand that you cannot filter on a virtual field, so that Year has to have it’s own element.

I can easily input the date. But I’m struggling to figure out how to extract the year and post it to it’s own container.

2

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format

So if you have field called my_date that holds a certain date (example: 2010-07-23) you would extract year with :

SELECT DATE_FORMAT(my_date, ‘%Y’) FROM my_table

3

actually, you can

you can write

WHERE YEAR(mydate) = $year [I]-- e.g. 2009[/I]

having a separate column populated with the year isn’t really necessary

you will likely have an index on mydate, for searching specific dates

however, applying a function to a column as in the above WHERE clause will prevent the use of an index on that column

to utilize the index, you would write

WHERE mydate >= '$year1day' [I]-- e.g. '2009-01-01'[/I]
  AND mydate  < '$year2day' [I]-- e.g. '2010-01-01'[/I]

note the “less than” on the upper bound