Page 1 of 1
search by month and year
Posted: Thu Dec 06, 2007 7:46 am
by qumar
hi friends,
i want search by month and year. how can i give, i am facing problem with this. please give some idea.
in database table, date foramte is like this 2007-12-06. i want search with 12-2007. i want display the records under each month. please help to me.
thanks,
qumar.
Choose the Right Board
Posted: Thu Dec 06, 2007 8:55 am
by s.dot
I would advise you to alter your database table, adding fields month, day, and year. This way you can search for what you want with a simple select query.
The way it is now, you'd have to select all dates, loop through the result set,
explode() on the '-' character, and then look for the month you want.
Moved to databases:
Re: Choose the Right Board
Posted: Thu Dec 06, 2007 9:12 am
by Zoxive
scottayy wrote:I would advise you to alter your database table, adding fields month, day, and year. This way you can search for what you want with a simple select query.
The way it is now, you'd have to select all dates, loop through the result set,
explode() on the '-' character, and then look for the month you want.
Not Necessary. Why not Just..
Code: Select all
Select * from `dates` where `date` >= '2007-12-01' AND `date` <= '2007-12-31'
Even yet..
Code: Select all
Select * from `dates` where MONTH(date) = 12 AND YEAR(date) = 2007
Posted: Thu Dec 06, 2007 10:25 am
by RobertGonzalez
Seriously, use the date functions in the database. They are a plenty and they are really useful. Even without changing your structure.
FYI, My date time formats are always 0000-00-00 00:00:00. Then I do the date math/manipulation on the DB server as needed.
Posted: Thu Dec 06, 2007 4:16 pm
by s.dot
I was thinking along those lines.. almost posted something about doing date conversion/comparing in the query, but I couldn't say it with enough confidence.. so I didn't say it.

Posted: Fri Dec 07, 2007 4:07 am
by Kieran Huggins
As an added bonus, any operation that you can push onto the database will speed up your app

Posted: Fri Dec 07, 2007 9:42 am
by RobertGonzalez
+1 K-Hug. I should have mentioned that as well.