Page 1 of 1

Selecting by date and wildcards

Posted: Fri Oct 17, 2003 5:42 am
by JayBird
I have a table where one of the fields contains dates in the format YYYY-MM-DD.

I am trying to select all the entries that occured in the current month.

Up to now, i have only figured out how to get all entrie for the current day by doing this

Code: Select all

select * from pis where pi_date = date_format(NOW(), '%Y-%m-%d')
Basically, i need some kind of wildcard for the Day, so it will select all days from the current month.

Thanks

Mark

Posted: Fri Oct 17, 2003 5:45 am
by JayBird
i tried this and it seems to work.

But is it the best way?

Code: Select all

select * from pis where pi_date like date_format(NOW(), '%Y-%m___')

Posted: Fri Oct 17, 2003 5:54 am
by twigletmac
Is pi_date in a DATE field type? If so you could try something like:

Code: Select all

SELECT field1, field2, field3 FROM pis WHERE YEAR(pi_date) = YEAR(NOW()) AND MONTH(pi_date) = MONTH(NOW())
http://www.mysql.com/doc/en/Date_and_ti ... tions.html

Mac

Posted: Fri Oct 17, 2003 6:04 am
by JayBird
yes, it is in a date field, and yes, your example works nicely....thank :)

Also, when the date is returned and displayed, i want it to be in the format of DD-MM-YYYY instead of YYYY-MM-DD.

Should i use PHP to reformat it, or is there a better way using MySQL?

Obviously, i know about date_format in MySQL as you can see from my example above, but not sure how to implement it into my query.

Thanks

Mark

Posted: Fri Oct 17, 2003 6:11 am
by twigletmac
To implement DATE_FORMAT() (which would be my preferred choice), you could do:

Code: Select all

SELECT field1, field2, field3, DATE_FORMAT(pi_date, '%d-%m-%Y') AS display_date FROM pis WHERE YEAR(pi_date) = YEAR(NOW()) AND MONTH(pi_date) = MONTH(NOW())
You can then access the formatted date as the display_date element from within the array generated by mysql_fetch_assoc().

Mac

Posted: Fri Oct 17, 2003 6:15 am
by JayBird
yey, worked a treat twiglet.

I bloody hate dates, they do my head. :)

Mark