rhgiant — 2011-07-25T12:23:42-04:00 — #1
I retrieve a date:
Now, I'd like to query another table and get all the records WHERE year = 2011 and month = 04.
In other words, based on a date retrieved, I'd like to find all records that are of the same year/month.
I have absolutely no idea where I shoudl start to do this...
r937 — 2011-07-25T13:14:15-04:00 — #2
when you say "i retrieve a date" what does that mean?
are you retrieving it in an application language like php? and are you sure there is only one row that will be returned? and are you happy to do your process using two separate queries?
if so, you have multiple date functions in php which will allow you to extract dates
when you generate the conditions for the query that you want to run, make sure you generate a date that is equal to the first day of the month of the retrieved date, and a second date that is equal to the first day of the following month
WHERE somedate >= '2011-04-01'
AND somedate < '2011-05-01'
notice it's "greater than or equal to" the first date, but it's "less than" the second date
that way, you won't ever have to worry about february 29th
rhgiant — 2011-07-25T15:16:35-04:00 — #3
"I retrieve a date" == I get it from a table based on an id, provided by user input.
The format I posted is the one that is used by the db (I mean: it looks like that in the table).
Do I have to worry about hours or not?
Thanks a lot Rudy
r937 — 2011-07-25T16:19:16-04:00 — #4
FYI it does ~not~ look like that in the table, that's just the default presentation format (mysql's internal date format is not readable by humans)
so are you happy running two queries, one to get the date from one table and a second query for the other table, or would you rather not do it in one query?
no, you do not have to worry about the hours
rhgiant — 2011-07-26T00:37:51-04:00 — #5
Doing it in one query would be an improvement.
r937 — 2011-07-26T03:25:39-04:00 — #6
so could you please show the first query, and at least the template of the second query without the WHERE clause