ddelmonte — 2010-07-23T11:06:02-04:00 — #1
I'm not sure where to post this.. It's a PHP/mySQL -related question.
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.
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.
djomla — 2010-07-23T11:14:47-04:00 — #2
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
r937 — 2010-07-23T11:46:22-04:00 — #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