Hi,

Would anyone know how to code a query to search a date field for certain things. For example, if I had a date in a date field with the format MM/DD/YYYY how would I search the database and only retrieve the data for just the month or a particular day. I would even like to search for dates within a month like from 01/01/2001 to 01/07/2001.

I can code the information to go into the database but I'm unsure how to code this type of query


Thanks in advance...........

I don't know how to do it with a DATE field, but with a DATETIME field you can query it like this:

SELECT field FROM table WHERE datetime_field >= ADDDATE(NOW(),INTERVAL -24 HOUR)

I like DATETIME fields because you might as well know the exact time, right? More data is better than less. And it lets you select records based on a time range, like the example above. You can change -24 HOUR to any number of hours or use DAY, MINUTE etc.

Edit: Also, MySQL stores the time in UTC time, so the time may not be the same as where you are. A little PHP can take care of that though, and make it display your local time.

Originally Posted by merlin

Hi,

Would anyone know how to code a query to search a date field for certain things. For example, if I had a date in a date field with the format MM/DD/YYYY how would I search the database and only retrieve the data for just the month or a particular day. I would even like to search for dates within a month like from 01/01/2001 to 01/07/2001.

I can code the information to go into the database but I'm unsure how to code this type of query


Thanks in advance...........

you can write following statment to retreive result

select * from tablename where between date1 and date2
use between keyword to range your date.

mail me if you dont solve this query
chetanrakesh@yahoo.com