Selecting by date and wildcards

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Selecting by date and wildcards

Post 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
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post 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___')
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post 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
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post 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
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post 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
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

yey, worked a treat twiglet.

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

Mark
Post Reply