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