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:
[url=http://forums.devnetwork.net/viewtopic.php?t=30037]Forum Rules[/url] Section 1.1 wrote:1. Select the correct board for your query. Take some time to read the guidelines in the sticky topic.

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.